extracting data from one format to a different format...

  • Thread starter Thread starter Majestic Glory
  • Start date Start date
M

Majestic Glory

I have a large database of company contacts that is formatted/entered as
follows:

ABC Company, Inc.
John Doe
123 Main Street NE
Des Moines, IA 52000
Phone: 123-456-7890
Fax: 098-765-4321
(e-mail address removed)

Each line is in it's own cell, but I need it in column format such as follows:
Company Name - First Name - Last Name - Address1 - Address2 - City - State -
Zip - Phone - Fax - Email

Any direction as to how I can easily do this? I'm using either 2003 or 2007
and have over 7,000 entries like this. PLEASE HELP as I don't want to spend
hours into days trying to do this manually! Thanks.
 
On Sat, 29 May 2010 17:07:01 -0700, Majestic Glory <Majestic
I have a large database of company contacts that is formatted/entered as
follows:

ABC Company, Inc.
John Doe
123 Main Street NE
Des Moines, IA 52000
Phone: 123-456-7890
Fax: 098-765-4321
(e-mail address removed)

Each line is in it's own cell, but I need it in column format such as follows:
Company Name - First Name - Last Name - Address1 - Address2 - City - State -
Zip - Phone - Fax - Email

Any direction as to how I can easily do this? I'm using either 2003 or 2007
and have over 7,000 entries like this. PLEASE HELP as I don't want to spend
hours into days trying to do this manually! Thanks.
Click to expand...

You need to provide more information.

1. In your header, you have two Address columns (Address1 and Address2) but in
your example you have only a single address line. Does this mean that some
entries have seven lines and others eight? What about those without a Fax? Or
those without and Email? Are there blank lines or no lines?

2. How do you tell when you go from one company to the next?

3. Are there any other variations you haven't mentioned?
--ron
 
If you have data for each contact in seven cells (rows 1-7, 8-14,21-27...) in
Col A
then enter this formula in B1
==INDIRECT("A"&((ROW()-1)*7+COLUMN()-1))
You can then copy it across to Col H and then down to row number n/7 where n
is the last row with your data...

You can then split the city, state and zip in Col E. You can also find and
replace FAX: PHONE: etc which you don't want.
 
Back
Top