Seperating Numbers and Text

[garungaha1]

I need to seperate the numbers and words in the same cell. Not all tex
and/or numbers are the same or are the same amount of characters.

Ex.


22454 IN


is all in the same cell

I want:

22454 to be in one cell and

IN in another cell to the right

Thank

 

[SidBord]

I assume that the original cell is text. If it is, then
you can look for the position of a blank, then separate the
two based on the blank location. If cell A1 has "22454 IN",
In cell A2: = Search(" ". A1)
In cell A3: = Left(A1,A2-1)
In cell A4: = Right(A1,Len(A1) - A2)
Or something like this.

 

[Gord Dibben]

Copy the column of text/numbers two times then run each of these macros on a
copied column.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
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

Sub RemoveNums()
'' Remove numeric characters from a string.
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 Not (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

Gord Dibben Excel MVP