Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting data from one format to a different format...
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 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting data from one format to a different format...
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
extracting data from one format to a different format...
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. "Majestic Glory" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Extracting time (hr:min) from date/time format | Excel Discussion (Misc queries) | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) | |||
how to format excel format to text format with separator "|" in s. | New Users to Excel |