# Formula For add nos in a cell

#### TPR

##### Member
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

##### Excel Ninja
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

##### Member
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

##### Active Member
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

#### Attachments

• 9.1 KB Views: 6

#### TPR

##### Member
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

##### Active Member
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

##### Member
My XL version is 2016

#### pecoflyer

##### Active Member
Yes, and did you see the other questions?

#### Khalid NGO

##### Excel Ninja
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

##### Excel Ninja
Or try this non-array formula :

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

Regards
Bosco

#### GraH - Guido

##### Well-Known Member
or... with Power Query

Code:
``````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"``````

#### Attachments

• 16.4 KB Views: 3

#### Khalid NGO

##### Excel Ninja
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

##### Well-Known Member
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