Find/Replace is probably the way to go, but you can automate the process using a macro.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>. The macro assumes your original data is in Column A, and the
results will be placed in Column C of the active sheet. This can be easily changed, but should get you started. It "FIND"'s each of the letters and replaces it with its two digit
equivalent number. Using the FIND method should execute more rapidly than looping through each of the cells individually.
Code:
Option Explicit
Sub PostCodesToNums()
Dim rSrc As Range, rDest As Range
Dim c As Range
Dim S As String, L As Long
Dim sFirstAddress As String
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set rDest = rSrc.Offset(columnoffset:=2)
With rDest
.EntireColumn.ClearContents
rSrc.Copy Destination:=rDest
.NumberFormat = "@"
End With
For L = Asc("A") To Asc("Z")
With rDest
S = Chr(L)
Set c = .Find(what:=S, _
LookIn:=xlValues, _
lookat:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
sFirstAddress = c.Address
Do
c.Value = _
Replace(c.Text, S, _
Format(Asc(S) - 64, "00"), _
compa=vbTextCompare)
Set c = .FindNext(after:=c)
If c Is Nothing Then Exit Do
Loop While c.Address <> sFirstAddress
End If
End With
Next L
End Sub
Bookmarks