+ Reply to Thread
Results 1 to 8 of 8

Postal Codes Format

  1. #1
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Postal Codes Format

    I have this column of postal codes entered with random format. Is it possible to format cells to '00000-0000' updated and as entered?

    79602
    76308
    77056
    77375-4489
    76308-2206
    76118
    65037
    77586
    76446 4375

    Becomes:

    79602-0000
    76308-0000
    77056-0000
    77375-4489
    76308-2206
    76118-0000
    65037-0000
    77586-0000
    76446-4375

    Thanks in advance

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Postal Codes Format

    Try using the Special/Postal Code format

    That will do what you want for most of your entries. The 1's that already have - in themwill not be affected, you will need to remove that (Find/Replace should work for that)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Postal Codes Format

    I tried that for instance the 79602 becomes 00007-9602 when I did the Zip Code + 4

  4. #4
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Postal Codes Format

    I have found a solution for postal codes in custom format type:

    Please Login or Register  to view this content.
    Thanks for the assistance

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Postal Codes Format

    I do think that you need to use formula for this.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Data Range
    A
    B
    1
    79602
    79602-0000
    2
    76308
    76308-0000
    3
    77056
    77056-0000
    4
    77375-4489
    77375-4489
    5
    76308-2206
    76308-2206
    6
    76118
    76118-0000
    7
    65037
    65037-0000
    8
    77586
    77586-0000
    9
    76446 4375
    76446-4375
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Postal Codes Format

    Thanks but the custom format seemed to work fine.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Postal Codes Format

    Quote Originally Posted by billgyrotech View Post
    Thanks but the custom format seemed to work fine.
    It is not going to work on cell like this

    76446 4375

  8. #8
    Banned User!
    Join Date
    03-06-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    295

    Re: Postal Codes Format

    You are correct. This occurrence was rare and I was able to manually correct it. I tried your formula but it didn't update any cells. I am learning each day and appreciate all the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formatting of US Postal Codes in Excel
    By TrafficTrader in forum Excel General
    Replies: 2
    Last Post: 10-03-2014, 01:44 PM
  2. Distance between Canadian Postal Codes
    By lesleyhollett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-17-2013, 10:47 AM
  3. [SOLVED] Format for Canadian Postal Codes
    By rmcc in forum Excel General
    Replies: 25
    Last Post: 07-24-2012, 02:04 PM
  4. IF and OR help for postal codes
    By Richard Bunt in forum Excel General
    Replies: 1
    Last Post: 10-09-2009, 06:30 AM
  5. Adding Canadian Postal Codes to Excel 07
    By bandaid8 in forum Excel General
    Replies: 5
    Last Post: 05-22-2009, 01:58 PM
  6. [SOLVED] Distances between Postal Codes
    By Irfan in forum Excel General
    Replies: 1
    Last Post: 05-31-2006, 05:55 PM
  7. Using excel 2003 cannot see Canadian Postal Codes
    By PW in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-08-2005, 03:25 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1