• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
 

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,
 

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

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 :)
 
Top