Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Extract numbers (Excel 2003; WIN XP)

    Hi all....does anyone have a quick formula that will 'extract' numbers from a text string---eg: if I enter SMITH 90, BROWN 60, JONES 120 into a cell, I am looking for a formula that will extract and add the 90+60+120 and give me a total of 270.....??...which I will then format and express as time (eg: 4.5 hrs).....thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    The User Defined Function below should do what you asked:

    <code>
    Public Function SumNums(strIn As String) As Double
    Dim strWkA As String, strWkB
    Dim I As Long
    strWkA = Trim(strIn)
    For I = 1 To Len(strWkA)
    If Asc(Mid(strWkA, I, 1)) >= 48 And Asc(Mid(strWkA, I, 1)) <= 57 Then
    strWkB = strWkB & Mid(strWkA, I, 1)
    Else
    strWkB = strWkB & " "
    End If
    Next I
    strWkB = Trim(strWkB)
    Do While Len(strWkB) > 0
    SumNums = SumNums + CDbl(Left(strWkB, InStr(strWkB & " ", " ")))
    strWkB = Trim(Right(strWkB & " ", Len(strWkB & " ") - InStr(strWkB & " ", " ")))
    Loop
    End Function
    </code>
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Extract numbers (Excel 2003; WIN XP)

    Thank you Legare.....I'm only a beginner-intermediate with Excel so I was hoping that there is an easier method, preferably one that only requires the use of a TRIM formula, for example, or something that isn't too complex for me to understand etc.....or maybe something that SUBSTITUTES text with "" and leaves only numbers which can be added..??..??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    Say you have these values in A1, A2 etc.
    Enter the following formula in B1:

    =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$40) ,1)),0),255)

    and confirm with Ctrl+Shift+Enter to turn it into an array formula.
    Fill down as far as needed.
    You can sum the results in another cell. If you wish, you can then hide column B.

    Source: Array Formulas.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Extract numbers (Excel 2003; WIN XP)

    I am feeling quite inadequate about all of this....what I have done is enter TEXT & NUMBERS into a single cell....eg: in B1, I have typed "Smith 60 Jones 60 Brown 90"......I want to add the 60+60+90 (the numbers in the cell--I am trying to keep them all in the same cell), so I think I need a formula that will remove the text and leave the numbers so that I can add them.....maybe it's not possible to add three different numbers that are in the same cell ??

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    Do you have a specific reason to do it like this? It's much easier if you enter the names in one column and the numbers in another column.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Extract numbers (Excel 2003; WIN XP)

    I can do that, Hans, but I would enter multiple names in one cell and multiple, different numbers in an adjacent cell...eg: b1 would contain Smith, Jones, Brown and cell C1 would contain 60, 60, 90....and then I could add the 60+60+90 in D1...??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    Again: do you have a specific reason for wanting to enter multiple names in one cell, and multiple numbers in one (different) cell? You are making things much more difficult than if you enter tha data in a standard table-like format.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    I don't think what you want can be done using a formula, at least not easily. Another VBA function you can use is

    Public Function SumNums(strIn As String) As Double
    Dim arr() As String
    Dim i As Integer
    arr = Split(strIn, " ")
    For i = LBound(arr) To UBound(arr)
    SumNums = SumNums + Val(arr(i))
    Next i
    End Function

    With a text such as Smith 60, Jones 60, Brown 90 or 60, 60, 90 in B1, the formula =SumNums(B1) will return 210.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract numbers (Excel 2003; WIN XP)

    I think that the only way you are going to be able to do this without a custom function like I gave you would be to enter each name and number in separate cells. My function really is easy to use. If you have SMITH 90, BROWN 60, JONES 120 in cell A1, then you can put =SumNums(A1) an any cell and get 270 as the result.
    Legare Coleman

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Extract numbers (Excel 2003; WIN XP)

    Thank you Hans and Legare.....I was able to use the VBA functions and it works great....as always, thank you for your patience and your knowledge....bye for now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •