Results 1 to 3 of 3

Thread: Want to convert postal codes to numeric values in Microsoft Excel

  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Want to convert postal codes to numeric values in Microsoft Excel

    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. #2
    Join Date
    Aug 2011
    Posts
    695

    Re: Want to convert postal codes to numeric values in Microsoft Excel

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

  3. #3
    Join Date
    Mar 2011
    Posts
    542

    Re: Want to convert postal codes to numeric values in Microsoft Excel

    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

Similar Threads

  1. Custom Boolean Values in Microsoft Excel
    By Thedevotee in forum MS Office Support
    Replies: 2
    Last Post: 15-02-2012, 05:05 PM
  2. How to round-off nearest currently values in Microsoft Excel
    By Dvimida in forum MS Office Support
    Replies: 4
    Last Post: 25-01-2012, 06:22 PM
  3. How to remove characters between Cell values in Microsoft Excel
    By Limitless in forum MS Office Support
    Replies: 2
    Last Post: 25-01-2012, 12:08 PM
  4. Replies: 2
    Last Post: 24-01-2012, 02:07 PM
  5. Convert a numeric value into English words in Microsoft Excel
    By Bavol in forum Software Development
    Replies: 4
    Last Post: 16-12-2009, 02:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,653,631.45434 seconds with 17 queries