Hi,

I have a Word Document (Office 2003) with lots of names and addresses that I
need in Excel so I can add them to another list to do a data import.

The data in Word is like this:

Joe's Bar and Grill
1000 Somewhere Dr
City ST Zip
Contact: Joe Schmoe
Phone: 800-000-000
Fax: 800-000-000
County: WhoKnows

If I copy and paste that into Excel, of, it puts it in the same column, each
line on a row.

This is how I need it in Excel (in Columns):

Company Address Contact Phone Fax County
Joe's..... 1000 S... Joe 800-... 800-. Whoknows

I need to get it into columns, but I don't want the words Contact, Phone,
Fax etc to show up next to all the names, phone numbers etc. I hope that
makes sense.

So...how do I do it?

Thanks,

Dan

Hi!

Maybe this will get you started in the right direction:

The sample you posted is in the range of Sheet1 A1:A7.

Enter this formula in B1 and copy down to B7:

=IF(ISERROR(FIND(":",A1)),A1,MID(A1,FIND(":",A1)+2 ,255))

B1:B7 will now look like this:

Joe's Bar and Grill
1000 Somewhere Dr
City ST Zip
Joe Schmoe
800-000-000
800-000-000
WhoKnows


Then you can get rid of the formulas by converting those to constants.

Now, how you proceed depends on whether each group has *exactly* the same
number of rows of info. Are there empty rows between each group?

You could use a formula like this on Sheet2 that will transpose the data
from Sheet1:

=INDEX(Sheet1!$B:$B,(ROWS($1:1)-1)*7+COLUMNS($A:A))

Copied across then down.

That will give you the result you're looking for:

Joe's..... 1000 S... Joe 800-... 800-. Whoknows

Biff

"Dan B" wrote in message
...
Hi,

I have a Word Document (Office 2003) with lots of names and addresses that
I need in Excel so I can add them to another list to do a data import.

The data in Word is like this:

Joe's Bar and Grill
1000 Somewhere Dr
City ST Zip
Contact: Joe Schmoe
Phone: 800-000-000
Fax: 800-000-000
County: WhoKnows

If I copy and paste that into Excel, of, it puts it in the same column,
each line on a row.

This is how I need it in Excel (in Columns):

Company Address Contact Phone Fax County
Joe's..... 1000 S... Joe 800-... 800-. Whoknows

I need to get it into columns, but I don't want the words Contact, Phone,
Fax etc to show up next to all the names, phone numbers etc. I hope that
makes sense.

So...how do I do it?

Thanks,

Dan

From Your Example if each of the following items is on its own line like
your example:
Joe's Bar and Grill
1000 Somewhere Dr
City ST Zip
Contact: Joe Schmoe
Phone: 800-000-000
Fax: 800-000-000
County: WhoKnows
Make sure there is a paragraph return after each line. Your might have
to turn-on "SHOW/HIDE" paragraph marks (look for the paragraph symbol
on your menu bar. You will also need to remove "Manual Line Breaks" and
any "Tabs" that are present. Tab looks like a -, and manual line break
will be a different symbol than a paragraph mark at the end of each
line. These are found in the Find and Replace popup menu - then select
"MORE" - then "SPECIAL"
Once the "tabs" and "manual line breaks" are removed:
Then do a SELECT ALL
Then on the MENU BAR - Select Table - Convert - Text to Table.
Then in the "Number of Columns" enter 7
Then in the "Columns Seperated By" Check "Paragraph Marks" and then
Click OK.
Now everthing should go into X number of rows and 7 Columns.
Then select one column at a time and in the FIND and REPLACE box -
enter what you want to delete and then say ok.
You will get a message that says "Items in Selection have been replaced
- Do you want to continue" select the NO Button.
Once you have your stuff cleaned-up - Copy the table and paste it into
EXCEL. In Excel you might select 7 Cells and Select text as the format
- and then paste special - paste as text. Then reformat your cells as
necessary.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=498040

that is another good idea...would have never thought of that one. Thanks!!

"wjohnson" wrote in
message ...

From Your Example if each of the following items is on its own line like
your example:
Joe's Bar and Grill
1000 Somewhere Dr
City ST Zip
Contact: Joe Schmoe
Phone: 800-000-000
Fax: 800-000-000
County: WhoKnows
Make sure there is a paragraph return after each line. Your might have
to turn-on "SHOW/HIDE" paragraph marks (look for the paragraph symbol
on your menu bar. You will also need to remove "Manual Line Breaks" and
any "Tabs" that are present. Tab looks like a -, and manual line break
will be a different symbol than a paragraph mark at the end of each
line. These are found in the Find and Replace popup menu - then select
"MORE" - then "SPECIAL"
Once the "tabs" and "manual line breaks" are removed:
Then do a SELECT ALL
Then on the MENU BAR - Select Table - Convert - Text to Table.
Then in the "Number of Columns" enter 7
Then in the "Columns Seperated By" Check "Paragraph Marks" and then
Click OK.
Now everthing should go into X number of rows and 7 Columns.
Then select one column at a time and in the FIND and REPLACE box -
enter what you want to delete and then say ok.
You will get a message that says "Items in Selection have been replaced
- Do you want to continue" select the NO Button.
Once you have your stuff cleaned-up - Copy the table and paste it into
EXCEL. In Excel you might select 7 Cells and Select text as the format
- and then paste special - paste as text. Then reformat your cells as
necessary.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile:
http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=498040