# Thread: Excel 2003 - Extract number function

1. I have a column of data that has verbage like so:

Individual copay is 30 etc etc.

I would like to create another column that contains just the 30.

Is there a function that I could add to my Persnal.xls that does this? Thanks for your help...

2. [quote name='LindaR' post='800356' date='28-Oct-2009 17:52']I have a column of data that has verbage like so:

Individual copay is 30 etc etc.

I would like to create another column that contains just the 30.

Is there a function that I could add to my Persnal.xls that does this? Thanks for your help...[/quote]

Or use this found at
http://www.automateexcel.com/2008/11/03/vb...er-from-string/

Code:
```Function Extract_Number_from_Text(Phrase As String) As Double
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Length_of_String = Len(Phrase)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(Phrase, Current_Pos, 1) = "-") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = ".") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
Next Current_Pos
If Len(Temp) = 0 Then
Extract_Number_from_Text = 0
Else
Extract_Number_from_Text = CDbl(Temp)
End If
End Function```

3. [quote name='HansV' post='800361' date='28-Oct-2009 18:12']See extracting numbers from within text (XL97 or 2)[/quote]

How would I use this function?

Function ExtractNumber(vValue)
Dim x As Integer
For x = 1 To Len(vValue)
If Val(Mid(vValue, x)) <> 0 Then
ExtractNumber = Val(Mid(vValue, x))
Exit Function
End If
Next
End Function

I will be putting the code in my Personal.xls. =Personal.xls!ExtractNumber(A2). Would this be the syntax?

4. [quote name='LindaR' post='800366' date='28-Oct-2009 19:27']I will be putting the code in my Personal.xls. =Personal.xls!ExtractNumber(A2). Would this be the syntax?[/quote]
Yes, that is correct.

5. [quote name='AndrewKKWalker' post='800364' date='28-Oct-2009 19:16']Or use this found at
http://www.automateexcel.com/2008/11/03/vb...er-from-string/

Code:
```Function Extract_Number_from_Text(Phrase As String) As Double
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Length_of_String = Len(Phrase)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(Phrase, Current_Pos, 1) = "-") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (Mid(Phrase, Current_Pos, 1) = ".") Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
Temp = Temp & Mid(Phrase, Current_Pos, 1)
End If
Next Current_Pos
If Len(Temp) = 0 Then
Extract_Number_from_Text = 0
Else
Extract_Number_from_Text = CDbl(Temp)
End If
End Function```
[/quote]

I used the above function and if the sentence has more than one number, it brings both and runs them together. Where in the code can you put a space between numbers if there is more than one number? Thanks for your help.l

6. [quote name='LindaR' post='800371' date='28-Oct-2009 19:06']I used the above function and if the sentence has more than one number, it brings both and runs them together. Where in the code can you put a space between numbers if there is more than one number? Thanks for your help.l[/quote]

It isn't my function, it was just free on the net.
There is no place in this function to obviously do that.
It just extracts the numbers from a string.

It doesn't look for multiple occurrences of a numbers and separate them.

You said you wanted to extract the number into a cell.
If you extract more than one number then the return value
would be text not a number.

What do you really want to get back out of the string.

7. If you want a function that extracts all Numbers out as a string separated by some character,
you could use this one.

You have to supply the Cell with the Phrase in and a suitable Separator to use when a number is found.
If only one it will be ignored

I am sure there are more elegant solutions, but it works for me.

It would be entered in the form

=ExtractNumbers(A1," / ") if for example you wanted numbers separated by space / space
==ExtractNumbers(A1," ") if you want them separated by a space

Code:
```Function ExtractNumbers(strPhrase As String, strSeperator As String) As String

Dim lngChars As Long
Dim strChar As String, strNext As String
Dim fNumber As Boolean
Dim strReturns As String
Dim lngChar As Long

Application.Volatile

lngChar = 1
lngChars = Len(strPhrase)

Do Until lngChar > lngChars
strChar = Mid(strPhrase, lngChar, 1)
If lngChar = lngChars Then
If IsNumeric(strChar) Then
strReturns = strReturns & strChar
End If
Else
strNext = Mid(strPhrase, lngChar + 1, 1)
If strNext <> "." And Not IsNumeric(strNext) And IsNumeric(strChar) Then
strReturns = strReturns & strChar & strSeperator
ElseIf strNext = "." And IsNumeric(strChar) Then
strReturns = strReturns & strChar & strNext
ElseIf IsNumeric(strChar) Then
strReturns = strReturns & strChar
End If
End If
lngChar = lngChar + 1
Loop

If strReturns <> "" And Right(strReturns, Len(strSeperator)) = strSeperator Then
strReturns = Left(strReturns, Len(strReturns) - Len(strSeperator))
End If

If Right(strReturns, 1) = "." Then
strReturns = Mid(strReturns, 1, Len(strReturns) - 1)
End If

ExtractNumbers = strReturns

End Function```

8. Thanks to everyone who contributed to this post.

#### Posting Permissions

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