# Thread: Extract Numbers from Text (Excel 2002)

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

2. ## 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 | Text-to-Columns wizard on it using the space delimiter.

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

4. ## Re: Extract Numbers from Text (Excel 2002)

<P ID="edit" class=small>(Edited by JohnBF on 08-Jun-07 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 Function

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

#### Posting Permissions

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