VBA – Extract Number From String
Extract Numerical Portion of String
The following function will extract the numerical portion from a string:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 |
So for example:
Extract_Number_from_Text(“uuigguo 995”) will return 995
Extract_Number_from_Text(“uuigguo 0.12995”) will return 0.12995
Extract_Number_from_Text(“yu00.000456”) will return 0.000456
=Extract_Number_from_Text(“juii-0009.9987iihiii”) will return -0.9987
If there is no number in the phrase then a zero is returned.
To download the .XLSM file from this article, click here.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
