Reformatting Data - Help Needed

kmeunier

New Member
Joined
Oct 2, 2006
Messages
24
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Oct 2, 2006
Messages
24
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Oct 2, 2006
Messages
24
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. :)
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top