Splitting String of Zips

[erinf07]

Hello --

I need help with the following...

I have four rows in excel, each with a list of comma separated zip codes:

A1: ’73101,73102,73103,73104,73105,73106,73107,73108
A2: ’75201,75202,75203,75204
A3: ’71823,71825,71826,71827
A4: ’15122,15201,15202,15203
etc.

I need to take the above and turn it into the following:

B1: “73101″, “73102″, “73103″, “73104″, “73105″, “73106″, “73107″, “73108″, “75201″, “75202″, “75203″, “75204″, “71823″, “71825″, “71826″, “71827″, “15122″, “15201″, “15202″, “15203″

So in plain english, I need to take a few rows of comma separated zip codes, put them in double quotes, then concatenate the zips together with a comma all in one row. A few issues:
- The first zip in each row starts with a single quote.. I need to get rid of that
- The last zip in each row does not have a comma.. I need to add a comma
- I need to then concatenate all the zips into one row using a formula, so that in the future, I can drop a bunch of zips in the format shown in A1 – A4 and have it spit out as shown in B1.

Can this all be done using formulas?

Any help here would be huge! Thanks!
Erin

[vlady]

maybe like this one.

Please Login or Register  to view this content.

Note: change my reference to A1 then fill handle drag down. Then concatenate the out come of those formulas.thanks

[erinf07]

Hi vlady --

That almost worked. Here are the snags:

Issue 1:
For rows with one zip code:
i.e. A1 = '75001,

The output with this formula is:

75001" , ""

Issue 2:
The first zip in the string still begins with the single quote.

I.e. A2 = '73001,73002,73003

The output with this formula is:

'73001", "73002", "73003"

Issue #3
I also need to add a comma at the end of each string... So a comma is needed above after "73003"

Let me know what you think. Really appreciate your help with this.

[vlady]

split.xlsx

I did not notice there is another character involve. look attachment for edited formula.