Sum a String

[bw]

My formula is =Sum(A1:A50), which of course works when the data is numeric.

However, when the data is a string, and the string has a "number" at the end
of it, I still want to sum those values at the end of the string. Can
someone explain how this is done?

Example Data
A1 = "Data in the first cell 12"
A2 = <null>
A3 = "Data in the second cell 345"
A4 = "Data in the third cell 6"
A5 = <null>
A6 = "This data is in the sixth cell 789"

So with ONLY ONE formula, I would like to sum the numbers in the cells
(12+345+6+789) to get 1152 as the result.

Suggestions please?
Thanks,
Bernie

 

[Biff]

Hi!

So with ONLY ONE formula

If you're holding out for a SINGLE formula you're going to be waiting a
looooooong time!

If the length of the string BEFORE the numbers was the same in all cells it
would be fairly easy. Since it appears that this is not the case, not so
easy!

Use a helper column and extract the numbers:

=IF(LEN(A1)>1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)*1,"")

Then SUM that helper column.

Biff

 

[Bob Phillips]

Bernie,

Use

=SUMPRODUCT(--(RIGHT(" "&"0"&rng,LEN(" "&"0"&rng)-
SEARCH("@"&" ",SUBSTITUTE(" "&"0"&rng," "&"","@"&" ",LEN(" "&"0"&rng)-
LEN(SUBSTITUTE(" "&"0"&rng," "&"","")))))))

where rng is the range you are working on

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[bw]

Thank you both !

I have saved both solutions in my "Tips and Tricks" file. I appreciate your
help.

Bernie

 

[Biff]

Very clever, Bob!

I was thinking of doing somehting along those lines but figured it was
easier to just extract the numbers in a helper.

That can be shortened a little by reducing some of the concatenation:

=SUMPRODUCT(--(RIGHT(" 0"&rng,LEN(" 0"&rng)-SEARCH("@ ",SUBSTITUTE("
0"&rng," "&"","@ ",LEN(" 0"&rng)-LEN(SUBSTITUTE(" 0"&rng," "&"","")))))))

Biff

 

[Bob Phillips]

You are absolutely right. The version that I have filed is structured to
take any sort of delimiter before the number. It has the delimiter in a
named cell, so it goes

=SUMPRODUCT(--(RIGHT(char&"0"& ...

For the OPs question, I just substituted " " for the char, didn't even
notice that " "&"0" is simpler as " 0" <G>

Regards

Bob

 

[Harlan Grove]

bw wrote...

My formula is =Sum(A1:A50), which of course works when the data is numeric.

However, when the data is a string, and the string has a "number" at the end
of it, I still want to sum those values at the end of the string. Can
someone explain how this is done?

Example Data
A1 = "Data in the first cell 12"
A2 = <null>
A3 = "Data in the second cell 345"
A4 = "Data in the third cell 6"
A5 = <null>
A6 = "This data is in the sixth cell 789"

So with ONLY ONE formula, I would like to sum the numbers in the cells
(12+345+6+789) to get 1152 as the result.

If you need to do this often, then you may be better off downloading
and installing Laurent Longre's MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

It contains an add-in function named REGEX.MID which could be used in
array formulas like

=SUM(--(REGEX.MID(A1:A6&" 0","\d+",1)))

Further, if your embedded numbers contained sign characters and decimal
fractions, you could adapt this formula to

=SUM(--REGEX.MID(A1:A6&" 0","[-+]?\d*\.?\d+",1))

 

[Ron Rosenfeld]

bw wrote...

My formula is =Sum(A1:A50), which of course works when the data is numeric.

However, when the data is a string, and the string has a "number" at the end
of it, I still want to sum those values at the end of the string. Can
someone explain how this is done?

Example Data
A1 = "Data in the first cell 12"
A2 = <null>
A3 = "Data in the second cell 345"
A4 = "Data in the third cell 6"
A5 = <null>
A6 = "This data is in the sixth cell 789"

So with ONLY ONE formula, I would like to sum the numbers in the cells
(12+345+6+789) to get 1152 as the result.

If you need to do this often, then you may be better off downloading
and installing Laurent Longre's MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

It contains an add-in function named REGEX.MID which could be used in
array formulas like

=SUM(--(REGEX.MID(A1:A6&" 0","\d+",1)))

Further, if your embedded numbers contained sign characters and decimal
fractions, you could adapt this formula to

=SUM(--REGEX.MID(A1:A6&" 0","[-+]?\d*\.?\d+",1))

Harlan,

(Trying to learn regular expressions).

Since the OP stated he wanted to add the numbers at the "end of the string",
how about:

=SUM(--(REGEX.MID("0 "&A1:A6,"\d+",-1)))


--ron

 

[Harlan Grove]

Since the OP stated he wanted to add the numbers at the "end of the string",
how about:

=SUM(--(REGEX.MID("0 "&A1:A6,"\d+",-1)))

That'd work.

 

[Ron Rosenfeld]

That'd work.

Thanks

--ron