i need to convert 80,000 postal codes to a 9 digit numeric value.
where M5R3T8 converts to 135183208
and M4P3J9 converts to 134163109
each letter of the alphabet needs to reflect the numbers 1 - 26.
a = 01
b = 02
c= 03
d= 04
e = 05
f = 06
g = 07
h = 08
i = 09
j = 10 etc
I am currently copying the postal codes to a separate sheet and doing a
find and replace. sounds crude, i know but it is the only way i know.
Hope you can help!
thanks!
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.
===================================================
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"), _
compare:=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
================================
