how do i get numbers from a text string

Z

Zack D

If I have a text string that includes numbers ex. "Zack 2345 West" how do I
pull out 2345 into a different cell? The location of the numbers will vary.
 
J

JulieD

Hi

if they are always four digits long, surrounded by a space then you can use
either the data / text to column option using space as the delimination
character or the following formula:
=MID(A1,SEARCH(" ",A1,1)+1,4)

Hope this helps
Cheers
JulieD
 
D

Don Guillett

From a mod of a post by Tom Ogilvy

Sub Tester15()
Dim sString As String, sStr As String
Dim i As Long, sChr As String
sString = ActiveCell 'Range("d1").Text
For i = 1 To Len(sString)
sChr = Mid(sString, i, 1)
If IsNumeric(sChr) Or sChr = "123" Then
sStr = sStr & sChr
End If
Next
MsgBox sStr

End Sub

--
Don Guillett
SalesAid Software
[email protected]
Zack D said:
If I have a text string that includes numbers ex. "Zack 2345 West" how do I
pull out 2345 into a different cell? The location of the numbers will
Click to expand...
vary.
 
G

Gord Dibben

Zack

Copy the column then run this macro on the copied column.

Sub RemoveAlphas()
'' Remove alpha characters from a string, including decimal point.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with macros, see David McRitchie's "getting started" site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
Top