How to sum the numbers in a text string?

[Tse-Wei Chen]

Hi

If I have a text string in a cell below:

"Path 8.5+Metrocard 10"

And I would like to return the result of the sum of the numbers 8.5+10

"18.5" (see attached image)

How can I do that by using the formula?

The text string might content more than two numbers like "Rent 1000+electric 100+sewage 50"

Is it possible to extract the numbers and sum them in one cell by formulas and functions?

Thanks

[#2]

Mr. Chen

copy the function below in a module then call it in the cell you preferred.

example:

A1 = Path 8.5

in cell B1 copy the formula =Digits(A1)*1

B1 will show 8.5

from there you can now do what you want.

**  try this in a test workbook -- always make back-up copy of your work.

HTH

Function Digits(ByVal S As String) As String 'courtesy of Rick Rothstein
 Dim X As Long
 For X = 1 To Len(S)
 If Mid(S, X, 1) Like "[!0-9,.]" Then Mid(S, X) = Chr(1)
 Next
 Digits = Trim(Replace(S, Chr(1), ""))
End Function

[#3]

Mr. Chen

I am attaching herewith a sample..

I hope it is what you need.

[#4]

prev attached file is a draft..   attached herewith is the correct sample

sorry...

[#5]

Thanks!

Is there any possibility to extract the multi digits of a text string and return the sum of these digits in one cell?

Say the text string "Path 8.5+Metrocard 10" is in one cell, and I have thousand cells with similar description, that I can't manually separate the text string into multi string like "Path 8.5" and "Metrocard 10" and use the digit function. 

Might need a way to extract the items in the text first? or there is a sophisticated way to do it in one step?

Thanks again

[#6]

Thanks!

Is there any possibility to extract the multi digits of a text string and return the sum of these digits in one cell?

Say the text string "Path 8.5+Metrocard 10" is in one cell, and I have thousand cells with similar description, that I can't manually separate the text string into multi string like "Path 8.5" and "Metrocard 10" and use the digit function.

Might need a way to extract the items in the text first? or there is a sophisticated way to do it in one step?



Thanks again

[#7]

Mr. Chen

I don't know if I can do it. But I think it is doable.

unfortunately, I am not an MVP!

but I will try - it is a challenge..

is there always a plus (+) sign between those words?

are the numbers always at the end of each word?

what is the maximum number of words in a cell?

[#8]

Yes, there are always numbers at the end of each word and they are all combined by "+".
The maximum number of words is 10 (eg. nine "+" to combine them).
And maximum number of characters is about 150.
It's definitely a challenge, I used to use notepad++ to replace these words and convert back to numbers and sum them, but I am looking for a better way to do it and see if anyone has some cool thoughts.

Thanks again!

[#9]

Mr. Chen

pls find as attached the sample for you to test.

I hope that it is what you needed.

A rather simple approach (though not that simple at all - I got help from somewhere!)

A much better way is by VBA - this is way out of my league!

but there are many good MVP's here in the forum in case you needed one.

good luck!

[#10]

Mr. Chen

attached herewith is the sample again for you to test - this time it had a direct formula - no need for Name Manager.

Hope this is what you needed.

thank you

[#11]

Mr. Chen

attached is the best solution (I reckon) for you to test

regards

[#12]

Sorry for the late reply I was in the project site.
This is exactly what I need!!!
It's awesome.
Did you create a "sumr" function to grab the data?
This function wasn't built in excel

Thanks

[#13]

Mr. Chen

Actually, it is outside of my 'powers'..

I sought help somewhere and some helpful people pitched in.

I properly attributed their work in the sub modules.

I am glad it helped you.

[#14]

That's a really great solution.

Thanks again.

[#15]

glad to help..

[#16]

Hi Kim,
could it sum up Path only?
Path 8.5+Metrocard 10+Rent 1000+Path 9+Metrocard 10+Rent 1000+Electric 1000+Sewage 50
Sum(Path)=8.5+9 =17.5

[#17]

@ForestSLZhang 

Try this:

=LET(arr,WRAPCOLS(TEXTSPLIT(A1,{" ","+"}),2),filtered,FILTER(1*TAKE(arr,-1),TAKE(arr,1)="Path"),SUM(filtered))