+ Reply to Thread
Results 1 to 4 of 4

Combined Post codes

  1. #1
    Registered User
    Join Date
    09-14-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Combined Post codes

    Hi All,

    This is the first time I have posted on here but I am kinda at my wits and I am currently working to correlate some data out of a .CSV generated from a database. The Problem with this report is that in the with a particular product in the report field labelled locations shows the locations as 3 postcodes joined together.

    For Example

    200027602200

    What I need to be able to do is convert this into the below information

    Sydney, Colyton, Bankstown

    Here is a little more info for you all Australian post codes are 4 digits and also with this report there are some products that only have one or 2 postcodes as per the below examples:

    3 Post Codes
    200027602200

    2 Post Codes
    20002760

    1 Post Code
    2000


    For each one of these examples I need the data to come back with in once cell:

    3 Post Codes
    Sydney, Colyton, Bankstown

    2 Post Codes
    Sydney, Colyton

    1 Post Code
    Sydney


    Yes I do have a list of all Australian Postcodes and their names.

    Thank you all for any help

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Combined Post codes

    Hi mikeconomy and welcome to the forum,

    I had an awful time with the problem because I found and opened the postal code csv file in Excel. None of my formulas worked. It was because the PCodes were all numbers and we need them as TEXT. To do this, use a blank column and create a formula like =A1 & "" and copy this down for all rows. Then copy and paste this column over the PCodes using Values Only to convert them to Text.

    Then look at my attached sheet and I think you'll see the formulas and get the idea.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Combined Post codes

    Something like this?

    Please Login or Register  to view this content.
    This could be taken ad infinitum, well, almost.

    see attached.

    cheers,
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Combined Post codes

    Hi Mike,

    Teylyn is smarter than I am so she didn't convert the codes to text. She did a "+0" to the lookup text to convert it to a number. That will work too, and most likely better.

    I was conderned as I found and downloaded the PCode file. Some of them were only 3 digits long. What happens then?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1