# Thread: Extract numbers (Excel 2003; WIN XP)

1. ## 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. ## 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>

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

11. ## 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
•