Forum Discussion

Tse-Wei Chen's avatar
Tse-Wei Chen
Copper Contributor
Aug 09, 2018

How to sum the numbers in a text string?

Hi

If I have a text string in a cell below:

"Path 8.5+Metrocard 10"

And I would like to return the result of the sum of the numbers 8.5+10

"18.5" (see attached image)

How can I do that by using the formula?

The text string might content more than two numbers like "Rent 1000+electric 100+sewage 50"

Is it possible to extract the numbers and sum them in one cell by formulas and functions?

 

Thanks

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor

    Mr. Chen

    copy the function below in a module then call it in the cell you preferred.

    example:

    A1 = Path 8.5

    in cell B1 copy the formula =Digits(A1)*1

    B1 will show 8.5

    from there you can now do what you want.

    **  try this in a test workbook -- always make back-up copy of your work.

    HTH

     

    Function Digits(ByVal S As String) As String 'courtesy of Rick Rothstein
    Dim X As Long
    For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9,.]" Then Mid(S, X) = Chr(1)
    Next
    Digits = Trim(Replace(S, Chr(1), ""))
    End Function

     

     

     

    • Tse-Wei Chen's avatar
      Tse-Wei Chen
      Copper Contributor

      Thanks!

      Is there any possibility to extract the multi digits of a text string and return the sum of these digits in one cell?

      Say the text string "Path 8.5+Metrocard 10" is in one cell, and I have thousand cells with similar description, that I can't manually separate the text string into multi string like "Path 8.5" and "Metrocard 10" and use the digit function. 

      Might need a way to extract the items in the text first? or there is a sophisticated way to do it in one step?

       

      Thanks again

      • Tse-Wei Chen's avatar
        Tse-Wei Chen
        Copper Contributor
        Thanks!

        Is there any possibility to extract the multi digits of a text string and return the sum of these digits in one cell?

        Say the text string "Path 8.5+Metrocard 10" is in one cell, and I have thousand cells with similar description, that I can't manually separate the text string into multi string like "Path 8.5" and "Metrocard 10" and use the digit function.

        Might need a way to extract the items in the text first? or there is a sophisticated way to do it in one step?



        Thanks again
    • Tse-Wei Chen's avatar
      Tse-Wei Chen
      Copper Contributor
      Sorry for the late reply I was in the project site.
      This is exactly what I need!!!
      It's awesome.
      Did you create a "sumr" function to grab the data?
      This function wasn't built in excel

      Thanks
      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor

        Mr. Chen

        Actually, it is outside of my 'powers'..

        I sought help somewhere and some helpful people pitched in.

        I properly attributed their work in the sub modules.

        I am glad it helped you.

         

  • Tse-Wei Chen 

    This is very close to Patrick2788.  We must go to the same Excel school!

     

    = LET(
        array, TEXTSPLIT(target, " ", "+", 1),
        value, VALUE(DROP(array,,1)),
        SUM(value)
      )

     

    I have used the TEXTSPLIT to generate a two column array rather than a single row.  The text column could be used for further filtering if required.

     

    Filtering on "Path" might be

     

    = LET(
        array, TEXTSPLIT(target, " ", "+", 1),
        text,  TAKE(array,,1),
        value, VALUE(DROP(array,,1)),
        SUM(FILTER(value, text="Path"))
      )

     

     

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor

      PeterBartholomew1 

      This request seems likes ages ago! Going back to the original request without path criteria:

       

      =LET(
          split, WRAPROWS(TEXTSPLIT(A1, {" ", "+"}), 2),
          SUM(TAKE(split, , -1) * 1)
      )
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor

        Patrick2788 

        I hadn't realised just how old the discussion is; answers now would not be of much value to the OP!

        On the other hand, past discussions can provide a rich vein of problems that I would never think of.  They allow me to evaluate and develop ways of working with modern Excel that bear little resemblance to past practice.  Usually the new approaches are far better, but its not always the case.

Resources