converting Postal codes to numeric value
 

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!

converting Postal codes to numeric value
 

Are the alphabet characters always upper case?
Do you know how to run/use VBA code?



"InNeedoflHelp"

wrote in message
...

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!
--
InNeedoflHelp

converting Postal codes to numeric value
 

On Fri, 10 Feb 2012 22:26:47 +0000, InNeedoflHelp wrote:


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"), _
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
================================

JIM:
I wish I did but I have no idea about VBA code.

converting Postal codes to numeric value
 

Try this basic VBA intro tutorial...
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

And then take another look at the code Ron Rosenfeld provided.
Good luck.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel: Date picker, Clean Data, Classic Menu ...)





"InNeedoflHelp"

wrote in message ...

JIM:
I wish I did but I have no idea about VBA code.
--
InNeedoflHelp



'Jim Cone[_2_ Wrote:
;1360306']Are the alphabet characters always upper case?
Do you know how to run/use VBA code?