Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,549
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default 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
================================
  #4   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by Jim Cone[_2_] View Post
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
JIM:
I wish I did but I have no idea about VBA code.
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,549
Default 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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Postal codes Karin Excel Discussion (Misc queries) 4 April 19th 08 05:43 PM
international postal codes JJ Johnson Excel Worksheet Functions 3 November 10th 07 04:23 PM
formula for Canadian postal codes LB Excel Worksheet Functions 7 July 18th 07 12:07 AM
UK Postal codes in Excel Paul G Excel Worksheet Functions 6 October 30th 06 01:07 PM
Distances between Postal Codes Irfan Excel Discussion (Misc queries) 1 May 31st 06 10:49 PM


All times are GMT +1. The time now is 11:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"