Forum Discussion

Copper Contributor

Aug 09, 2018

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 ...

excel

Formulas and Functions

Lorenzo Kim

Bronze Contributor

Aug 14, 2018

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.

Tse-Wei Chen

Copper Contributor

Aug 17, 2018

That's a really great solution.

Thanks again.

Lorenzo Kim
Bronze Contributor
Aug 17, 2018
glad to help..
- ForestSLZhang
  Copper Contributor
  Mar 10, 2023
  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
  - Patrick2788
    Silver Contributor
    Mar 13, 2023
    ForestSLZhang
    Try this:
    =LET(arr,WRAPCOLS(TEXTSPLIT(A1,{" ","+"}),2),filtered,FILTER(1*TAKE(arr,-1),TAKE(arr,1)="Path"),SUM(filtered))