Help!!

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

Im using Excel 2000 and I have a column full of UK
postcodes, e.g. WD6, E2, E1, HU14 etc...

What I want to do it to strip out the numbers and leave
the chars, so E11 would be E, WD6 would be WD etc...

As there could be two chars and two numbers, e.g. EG22,
and many variations, then this will need to be addressed.

What function do I use for this??

Please help.

skc
 
skc

Because of the variations, it might take several different formulas to strip
the numbers.

The following macro allows selecting all cells then stripping in one go.

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

If unsure about macros, see David McRitchie's "getting started" site or post
back for more details.

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

Gord Dibben Excel MVP
 
Back
Top