1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by TPR, Nov 30, 2018.

  1. TPR

    TPR Member

    Messages:
    51
    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
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,382
    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)
    Thomas Kuriakose likes this.
  3. TPR

    TPR Member

    Messages:
    51
    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.
  4. pecoflyer

    pecoflyer Active Member

    Messages:
    274
    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

    Attached Files:

  5. TPR

    TPR Member

    Messages:
    51
    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
  6. pecoflyer

    pecoflyer Active Member

    Messages:
    274
    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
    Peter Bartholomew likes this.
  7. TPR

    TPR Member

    Messages:
    51
    My XL version is 2016
  8. pecoflyer

    pecoflyer Active Member

    Messages:
    274
    Yes, and did you see the other questions?
  9. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,970
    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,
  10. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,037
    Or try this non-array formula :

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

    Regards
    Bosco
    Khalid NGO and (deleted user) like this.
  11. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    852
    or... with Power Query

    Code (vb):

    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"
     

    Attached Files:

    Khalid NGO likes this.
  12. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,970
    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.
  13. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    852
    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 :)
    Khalid NGO likes this.

Share This Page