Formula For add nos in a cell

[TPR]

Dear Friends,

My cells are carrying numbers in this format.
229.00~~ 700.25 (two ~ & one space is separating two numbers)
I need a formula that will give me result 929.25

Please help.
Regards
TPR

 

[Luke M]

You have two unique pieces of info in single cell. Don't do that.

We could use Text-to-columns to split the data apart, using the "~" and space as delimiters. This will put one piece of info in each cell, and also convert the text to numbers. Then you could easily add the numbers.

Or, if you really want to go down this path...
=LEFT(A2,FIND("~",A2)-1)+MID(A2,FIND(" ",A2)+1,999)

 

[TPR]

You have two unique pieces of info in single cell. Don't do that.

We could use Text-to-columns to split the data apart, using the "~" and space as delimiters. This will put one piece of info in each cell, and also convert the text to numbers. Then you could easily add the numbers.

Or, if you really want to go down this path...
=LEFT(A2,FIND("~",A2)-1)+MID(A2,FIND(" ",A2)+1,999)

Gr8. But Mr. Luke I gave an instance, I have multiple nos in the same pattern in one cell like this one:
3900.00~~ 16047.20~~ 20808.00~~ 4108.80
Even more........
Pls help me in such cases.

 

[pecoflyer]

Say your string is in A1
Go to the Formula Tab - Click Define Name - Enter a name ( say Test)
In "Refers to" enter the following =EVALUATE(SUBSTITUTE(TRIM(SUBSTITUTE(Sheet1!$A1,"~"," "))," ","+"))
click OK
In B1 enter =Test
That's it
Oh yes : remember to save your file as XLSM, NOT as XSLX

 

[TPR]

Say your string is in A1
Go to the Formula Tab - Click Define Name - Enter a name ( say Test)
In "Refers to" enter the following =EVALUATE(SUBSTITUTE(TRIM(SUBSTITUTE(Sheet1!$A1,"~"," "))," ","+"))
click OK
In B1 enter =Test
That's it
Oh yes : remember to save your file as XLSM, NOT as XSLX

Dear Sir,
Sorry I am not getting my result. My result should come 44864. Moreover I have about 1000 rows like same string, how many names would I define ?

Regards
TPR

 

[pecoflyer]

Please don't quote entire posts unnecessarily.

What do you mean by " I am not..."?
Are macros enabled on your sheet? (EVALUATE is actually a macro embedded in XL)
Which is your XL version?

No you don't have to create more names ( that would be tedious, don't you think?)
Just pull the =Test down. That is why the I used the relative reference $A1 in the formula

 

[TPR]

My XL version is 2016

 

[pecoflyer]

Yes, and did you see the other questions?

 

[Khalid NGO]

Hi,

Check this {array formula}

=SUM((TRIM(MID(SUBSTITUTE(A2,"~",REPT(" ",999)),1+(ROW($1:$999)-1)*999,999))&"0")+0)

{array formula needs to be entered with Ctrl+Shift+Enter}

Regards,

 

[bosco_yip]

Or try this non-array formula :

=SUMPRODUCT(--TEXT(MID(SUBSTITUTE(A1,"~",REPT(" ",99)),ROW($1:$99)*99-98,99),"@\0"))

Regards
Bosco

 

[GraH - Guido]

or... with Power Query

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  SumOfText = Table.AddColumn(Source, "SumOfText", each List.Sum(List.Transform(Value.FromText(Text.Split([Values],"~~ ")), each Number.FromText(_)))),
  #"Removed Columns" = Table.RemoveColumns(SumOfText,{"Values"})
in
  #"Removed Columns"

 

[Khalid NGO]

Hi @GraH - Guido
Amazing.

I tried with PQ Text to Column, but it didn't worked the way i wanted.

Good solution.
Thanks for sharing.

 

[GraH - Guido]

Thx for the sheers @Khalid NGO .
Still too much nesting going on, I wanted a shorter solution. Beats me why the list members must be declared as number type, where value from text is used. Okay, list by default are variant, but still... Still learning