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

  • Eval.xlsm
    9.1 KB · Views: 6
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
 
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
 
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,
 
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

  • sumoftextPQ.xlsx
    16.4 KB · Views: 3
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 :)
 
Back
Top