[Closed] Excel problem - summing multiple number values in a text cell

You can find the position of the / using a formula and extract the text to convert to a number.
The simple way would be to sort out your data source properly otherwise it will be unreliable.

Data tab>text to columns> delimited, then other and enter / in the box

are there only ever two backslashes?

You can do lots of fettling with search depending on how many values you expect. If it's heaps, it will get unwieldy.

https://support.office.com/en-us/article/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68

if there are just two then...

=LEFT(A1,(FIND("/",A1)-1)*1)+LEFT(RIGHT(A1,LEN(A1)-FIND("/",A1)),(FIND("/",RIGHT(A1,LEN(A1)-FIND("/",A1)))-1)*1)+RIGHT(A1,LEN(A1)-(FIND("/",A1)*1+FIND("/",RIGHT(A1,LEN(A1)-FIND("/",A1)))*1))*1

where the text entry is in cell A1

Slow lunch today...

OK, I googled for this.

Create a custom function:
1. Press Alt + F11.
2. Choose Insert→Module in the editor.
3. Type this code,
Function Sumthem(Rng As Range) As Variant

Sumthem = Evaluate(Application.Substitute(Rng, "/", "+"))

End Function

4. Save the function.
5. Return to Excel.
6. Use your user defined function just like you would any other by either typing =Sumthem(A1) or finding the function usign the 'Insert Function' button and looking under 'User Defined'

Note I called the function 'Sumthem' change it to whatever you fancy and I used A1 as an example but your array will be in whatever cell it is in

BTW it is rather simple and assumes you have numbers separated with a / that can be changed to a + and treat the whole lot as a simple calculation.  Chuck in anything more complicated and it may go rather wrong

The simple way would be to sort out your data source properly otherwise it will be unreliable.

Haha are you a SOLA? can't ask the customer to change their workbook.

Data tab>text to columns> delimited, then other and enter / in the box

I want to do it automatically, on the fly for use I other calculations in the workbook not just apply it to a static set of data. Thanks though.

are there only ever two backslashes?

Good question. No. Any amount of backslashes (within reason)

Rubber_Buccaneer - I tried that, it's similar to what I first tried where I used a substitute function to change the backslash to a '+' symbol, but excel does not want to evaluate the resulting sum.

Thanks to everyone so far.

likes the rubber_buccaneer solution 🙂

remember that your xlsx file now needs to become an xlsb file as it will have a macro/vba code in it (I think)

 I tried that, it’s similar to what I first tried where I used a substitute function to change the backslash to a ‘+’ symbol, but excel does not want to evaluate the resulting sum.

You created the custom function?  I tested it in Excel 2007 and it worked as expected

remember that your xlsx file now needs to become an xlsb file as it will have a macro/vba code in it (I think)

leffeboy is right about this.  You should get a warning when you go to save

I've no idea about the answer to your question, but that's not a backslash, it's a forward slash (or if you like, merely a slash).

Thanks Cougar, I never remember, my brain can't seem to work it out.

You created the custom function?  I tested it in Excel 2007 and it worked as expected

The evaluate function no longer worked directly after Excel 2010 IIRC

Sorry Stevet1, working in a cutting edge IT environment means I'm using twelve year old office on a fifteen year old desktop☹

Just tried my suggestion on Office 365 at home and it didn't work.

So, from a different angle here is another suggestion/variation

1. say A1 contains 11/12/13/14

2. Stick your cursor on B1

3. go to the 'Formulas' menu and choose 'Define Name' from the ribbon

4. enter a name, e.g. sumthem

5. in the Refers to: box type =EVALUATE(SUBSTITUTE(A1,"/","+"))

6. in B1 type =sumthem and hit enter

and that works for me in Excel 365. You can tweak for your data. As described I think it will always be applied to the data in the cell to the left and can be autofilled, pasted etc like any formula

Hope it works this time