Extracting numbers from text.
Posted by Bob T on October 19, 2001 5:17 PM
Is there a formula that I can extract the numbers out of a text string? example: 2DS=2 or 308 E Valley 4th floor = 3084.
Thanks for your help.
Posted by T Benjamin on October 20, 2001 11:11 PM
Try using the replace or substitute function to see if they give you the desired result
Posted by Robert Moore on October 21, 2001 4:24 PM
Try this macro.
It might be a bit slow - must be a better way to do it.
Sub Extract_Numbers()
Dim rng As Range, x%
If Selection.Columns.Count <> 1 Then
MsgBox "The selection can be in one column only."
Exit Sub
End If
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "The selection must be in the sheet's used range"
Exit Sub
End If
Application.ScreenUpdating = False
rng.Resize(, 2).EntireColumn.Insert
With rng.Offset(0, -2)
.FormulaR1C1 = "=SUBSTITUTE(RC[2],CHAR(1),"""")"
.Copy
.Offset(0, 1).PasteSpecial Paste:=xlValues
For x = 2 To 47
.FormulaR1C1 = "=SUBSTITUTE(RC[1],CHAR(" & x & "),"""")"
.Copy
.Offset(0, 1).PasteSpecial Paste:=xlValues
Next
For x = 58 To 255
.FormulaR1C1 = "=SUBSTITUTE(RC[1],CHAR(" & x & "),"""")"
.Copy
.Offset(0, 1).PasteSpecial Paste:=xlValues
Next
.Copy
.PasteSpecial Paste:=xlValues
.Offset(0, 1).EntireColumn.Delete
End With
End Sub
