Excel 2003 – Extract number function

Topic

New Reply

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…

Reply | Quote

Replies

See extracting numbers from within text (XL97 or 2)

Reply | Quote

See extracting numbers from within text (XL97 or 2)

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?

Reply | Quote

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

Yes, that is correct.

Reply | Quote

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…

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

Reply | 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

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

Reply | 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

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.

Reply | Quote

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

Reply | Quote

Thanks to everyone who contributed to this post.

Reply | Quote