Extract numbers from text

Routine #8 from our series of text subroutines to use in EXCEL to extract all numbers from a string. All the text contents in the selected range where this routine is applied will only have numbers, if numbers are not in the string the cell will be empty.

Code snippet below:

Sub Extract_Number_from_string() 'get numbers from text
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Dim old_value As String

Dim selected_range As Range
Set selected_range = Range(Selection.Address)

For Each cell In selected_range

old_value = vbNullString: old_value = cell.Value
Length_of_String = Len(old_value)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(old_value, Current_Pos, 1) = "-") Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
End If
If (Mid(old_value, Current_Pos, 1) = ".") Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
End If
If (IsNumeric(Mid(old_value, Current_Pos, 1))) = True Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
 End If
Next Current_Pos

If Len(Temp) = 0 Then
 cell.Value = ""
Else
 cell.Value = Temp
End If

Next cell

End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>