Forum Discussion
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 ...
Mr. Chen
I am attaching herewith a sample..
I hope it is what you need.
prev attached file is a draft.. attached herewith is the correct sample
sorry...
- 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 againMr. 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!
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?
- 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!
