Reformatting Data - Help Needed

[kmeunier]

Hello everyone!
I really could use some help with regards to reformatting some list data. The data I have looks like:

CHICAGO
ABC COMPANY
1415 N DRAKE AVE. STE 2 CHICAGO, IL 60625
P 312-267-1300
F 312-583-2701
T 312-583-2700

DAYTON
EFG COMPANY
3150 US HWY 1 STE B DAYTON, OH 45458
P 937-588-1000
F 937-588-1100
T 937-588-1101


I need the data formatting into columns in order to update our database. I don't the city names directly above each company, i.e. CHICAGO and DAYTON.

I noticed that the data which preceeds the CITY, ST and ZIP is not consistently spaced, i.e. sometimes there are multiple spaces before the CITY name begins, and other times it's just 1 space.

Does anyone have any suggestions how to do this? Ideally I'd like to end up with the following columns, without commas and periods:

COMPANY
ADDRESS1
ADDRESS2
CITY
STATE
ZIP
PHONE
FAX
TOLL FREE

Thank you so much for all of your help!

Kathy M.

 

[dave3009]

Hi There

If I read this right you need to remove trailing spaces, periods and commas for your data list. If you use a helper column you could try something like

=TRIM(SUBSTITUTE(SUBSTITUTE(A1, ",", ""), ".", ""))

And copy it down, this will remove the offending spaces periods and commas from the cells. You can then copy this and then Paste Special over the old data giving you clean data.

Hope It Helps


Dave

 

[kmeunier]

That would probably be step 1, but I still need a way to reformat the data in the specified columns. For example - ADDRESS1, ADDRESS2, CITY, STATE AND ZIP are all in one cell, and they need to broken into separate columns so that I can load the data into a database. That's the biggest (most time-consuming) issue I have right now.

Thank you!

Kathy M

 

[dave3009]

Hi Kathy

You could use Text to Columns for splitting up your data, go to Data>>Text To Columns>>Fixed Width click next then move the lines until you get the split right for address lines etc then Finish and it'll split the data up for you.

Then you can work on Trimming it to size

HTH


Dave

 

[kmeunier]

Hi Dave,
That wouldn't work - all of the data is in the same column on different rows, the first row is the COMPANY, the 2nd row is ADDRESS1, ADDRESS2, CITY, STATE & ZIP formatted all together in the same cell, etc.

I need the data split into separate columns:
COMPANY, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE, FAX, TOLL FREE in the same row.

Because the data is in different rows - I can't get it to work using TEXT to COLUMNS and have the end result in the same row. Also.... the address data isn't aligned consistently, nor does it have delimiters.

This is starting to look grim.