# How to sum the numbers in a text string?

Copper Contributor

# How to sum the numbers in a text string?

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

19 Replies

# Re: How to sum the numbers in a text string?

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`

# Re: How to sum the numbers in a text string?

Mr. Chen

I am attaching herewith a sample..

I hope it is what you need.

# Re: How to sum the numbers in a text string?

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

sorry...

# Re: How to sum the numbers in a text string?

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

# Re: How to sum the numbers in a text string?

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

# Re: How to sum the numbers in a text string?

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?

# Re: How to sum the numbers in a text string?

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!

# Re: How to sum the numbers in a text string?

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!

# Re: How to sum the numbers in a text string?

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

# Re: How to sum the numbers in a text string?

Mr. Chen

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

regards

# Re: How to sum the numbers in a text string?

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

# Re: How to sum the numbers in a text string?

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.

# Re: How to sum the numbers in a text string?

That's a really great solution.

Thanks again.

# Re: How to sum the numbers in a text string?

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

# Re: How to sum the numbers in a text string?

Try this:

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

# Re: How to sum the numbers in a text string?

This is very close to @Patrick2788.  We must go to the same Excel school!

``````= LET(
array, TEXTSPLIT(target, " ", "+", 1),
value, VALUE(DROP(array,,1)),
SUM(value)
)``````

I have used the TEXTSPLIT to generate a two column array rather than a single row.  The text column could be used for further filtering if required.

Filtering on "Path" might be

``````= LET(
array, TEXTSPLIT(target, " ", "+", 1),
text,  TAKE(array,,1),
value, VALUE(DROP(array,,1)),
SUM(FILTER(value, text="Path"))
)``````

# Re: How to sum the numbers in a text string?

This request seems likes ages ago! Going back to the original request without path criteria:

``````=LET(
split, WRAPROWS(TEXTSPLIT(A1, {" ", "+"}), 2),
SUM(TAKE(split, , -1) * 1)
)``````

# Re: How to sum the numbers in a text string?

I hadn't realised just how old the discussion is; answers now would not be of much value to the OP!

On the other hand, past discussions can provide a rich vein of problems that I would never think of.  They allow me to evaluate and develop ways of working with modern Excel that bear little resemblance to past practice.  Usually the new approaches are far better, but its not always the case.