Break One Column into Five

[nmlr]

Hi,

I am currently in the middle of a major project for my work that I'm excited to be working on except for a preliminary step of what amounts to torturous busy work.

I have to create a spreadsheet of the following data formatted n eight separate columns:

Date (MM/DD/YYYY)
Name
Address
City
ST
Zip Code
Occupation
Amount

The only problem is that my source formats it into just four columns:

Date
Name - Address, City ST #####
Occupation
Amount

The 2nd column's formatting of " - " in between Name and Address; ", " between Address and City, and spaces between City/ST and ST/Zip is universal. I just need something that can break that one column into five.

Normally, this kind of a task I would just do by hand but with 1000s of entries I could use a quick fix.

Is there anybody kind enough to design a macro for charity to do this? I can use Excel 2007 or 2003 if that makes a difference.

[nmlr]

Oh and I should note that I tried "Text to Columns" but the results were inconsistent due to the space deliminter (two word city names like Fort Lauderdale would be broken up into two columns instead of left as one).

[geng]

have you tried fixed width?

[nmlr]

I have, but addresses and city names vary in length to the point where finding good break lines is equally impossible.

I guess what I would need is something that would know to replace " - " ", " and ONLY the spaces before and after " ST " into breaks. I don't know if that's possible either.

[shg]

Post a sanitized workbook with a meaningful-sized sample of data.

[nmlr]

Alright.... here's a small santized sample with two common corporate addresses... the first sheet is the source and the second is what I need to get it to look like

[shg]

Select H3:L3 and array-enter =x(C3) and copy down.

This would be better implemented with regular expressions ...

Please Login or Register  to view this content.

[shg]

Or, with regular expressions,

Please Login or Register  to view this content.

[nmlr]

Thank you so much! The first one works perfect!