Combined Post codes

**mikeconomy** · 09-14-2011, 10:13 PM

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

**MarvinP** · 09-14-2011, 11:22 PM

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.

**teylyn** · 09-14-2011, 11:28 PM

Something like this?

Please Login or Register  to view this content.

This could be taken ad infinitum, well, almost.

see attached.

cheers,

**MarvinP** · 09-14-2011, 11:39 PM

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?