How do I seperate a alphabet and number in a cell-in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I seperate a alphabet and number in a cell - Ex. IWF49 to IWF in one
cell and 49 in a seperate cell, using formula, in Excel sheet.
 
If data is consistent you could use a formula as Anne shows.

If not consistent, perhaps a couple of macros could help.

Copy the column then run these two macros, one on each 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
 
Back
Top