Results 1 to 5 of 5

20070608, 03:44 #1
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
Extract Numbers from Text (Excel 2002)
Hi!
I'm wondering if there is a formula in Excel (or better yet, code in Visual Basic) that will extract a number from a line of text? For example,
the following text is in cell A1:
COM 235643 Sales Order
In most cases the number is in the same spot, therefore I could use the mid formula... however, there is always an exception to the rule, so I thought I'd ask if there was any other kind of formula that could locate the number and extract it into a cell by itself. Also note that in most cases the number is 6 digits, however at times it can be 4 digits... again an exception to the rule. Any ideas would be greatly appreciated.
Thanks!
Lana

20070608, 13:24 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Extract Numbers from Text (Excel 2002)
You could create a formula or a macro, but the fastest way may be to make a copy of the column and run the Data  TexttoColumns wizard on it using the space delimiter.
John ... I float in liquid gardens
UTC 7ąDS

20070608, 13:58 #3
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract Numbers from Text (Excel 2002)
The following function will extract the first number (all digits) it encounters.
Function getNums(strIn As String) As Double
Dim i As Integer, outNum As Double
outNum = 0
For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Then
outNum = Val(Mid(strIn, i, 99))
Exit For
End If
Next
getNums = outNum
End Function

20070608, 15:37 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Extract Numbers from Text (Excel 2002)
<P ID="edit" class=small>(Edited by JohnBF on 08Jun07 08:37. sign option choked on spaces)</P>Nice. Here's an extension that will optionally look for a leading negative sign or open paren:
Function getNums(strIn As String, Optional boolNeg As Boolean = False) As Double
' returns the first sequence of numbers within a cell
' spaces, '$' and comma signs are parsed out, decimal separators are included
' optional " ,TRUE" argument looks for preceding negative or open paren sign
Dim intC As Integer, strLeadChar As String
getNums = 0
For intC = 1 To Len(strIn)
If IsNumeric(Mid(strIn, intC, 1)) Then
getNums = Val(Mid(strIn, intC))
Exit For
End If
Next
If boolNeg = True Then
strLeadChar = Mid(strIn, InStr(1, strIn, Left(getNums, 1), vbTextCompare)  1, 1)
If strLeadChar = "" Or strLeadChar = "(" Then getNums = "" & getNums
End If
End FunctionJohn ... I float in liquid gardens
UTC 7ąDS

20070608, 15:54 #5
 Join Date
 Mar 2004
 Location
 Essex, England
 Posts
 93
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract Numbers from Text (Excel 2002)
When you extract a number from text spaces are ignored. Therefore, another trick worth knowing is to substitute a single space for a number of spaces.
In cell B1, you could also type the following formula :
=VALUE(MID(SUBSTITUTE(A1," "," "),SEARCH(" ",SUBSTITUTE(A1," "," ")),10))
Regards
Robert