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
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.
One test is worth a thousand opinions.
Click the * below to say thanks.
Something like this?
This could be taken ad infinitum, well, almost.=VLOOKUP(LEFT(A2,4)+0,PostCodeLookup,2,FALSE)&IF(LEN(A2)>4,", "&VLOOKUP(MID(A2,5,4)+0,PostCodeLookup,2,FALSE),"")&IF(LEN(A2)>8,", "&VLOOKUP(MID(A2,9,4)+0,PostCodeLookup,2,FALSE),"")
see attached.
cheers,
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?
One test is worth a thousand opinions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks