Results 1 to 11 of 11

20061206, 02:51 #1
 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 stringeg: 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

20061206, 05:10 #2
 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

20061206, 05:19 #3
 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 beginnerintermediate 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..??..??

20061206, 06:12 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20061206, 06:38 #5
 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 cellI 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 ??

20061206, 06:41 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20061206, 06:45 #7
 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...??

20061206, 07:11 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 tablelike format.

20061206, 08:15 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20061206, 15:55 #10
 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

20061206, 18:17 #11
 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