Sum Multiple Values In A Single Cell

[RUCEE]

I am trying to sum multiple values contained in single cell.
I have the following data in Column A:
9.5/4/6
3/4/6.5/7.8

My list goes on for over 700 entries.
The number of values separated by a slash changes as does the number of decimal points in each value.

I need to get the sum of each row to show up in Column B.

I prefer a non VBA solution if possible.

 

[Matty]

Welcome to the Board...

This would be tricky to do via formula.

Have you considered Text to Columns with a "/" Delimiter?

Matty

 

[Smitty]

Welcome to the Board!

Can you parse it out with Data-->Text to Columns-->Delimited-->"/"?

Then just sum from the last new column back. That will be much easier.

 

[VoG]

Or a UDF that goes in a regular module

Function SplitSum(r As Range) As Double
Dim i As Integer, X
X = Split(r.Value, "/")
For i = LBound(X) To UBound(X)
    SplitSum = SplitSum + X(i)
Next i
End Function

To use

=splitsum(A1)

 

[RUCEE]

I could separate the data into columns but it would make the sheets messy and not very dynamic. Sometimes there is only 1 value and others there are 6.

 

[pgc01]

Hi Rucee
Welcome to the board

Try:

=SUM(0+(0&MID(A1,FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})),FIND("#",SUBSTITUTE(A1&"//////","/","#",{1,2,3,4,5,6}))-FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})))))

 

[VoG]

Hi Rucee
Welcome to the board

Try:

=SUM(0+(0&MID(A1,FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})),FIND("#",SUBSTITUTE(A1&"//////","/","#",{1,2,3,4,5,6}))-FIND("#",SUBSTITUTE("/"&A1&"//////","/","#",{1,2,3,4,5,6})))))

Good grief! It'll take me at least a week to figure out how that works

 

[Matty]

I had a play around with a few options, but failed miserably...

Respect, pgc01

Now to figure out how it works!

Matty

 

[pgc01]

Well, I was just faster than you this time.

 

[RUCEE]

VoG, your solution works the best so far. Only one problem: after I copy the formula down for all of my entries, I cannot sum that column. I get a #Value! error.


pgc01, your solution works also, but only up to 6 values. Anyway to make it not care how many values there are? I do like that it is a formula however.


Thanks all for your help.