Need help with some data manipulation

[Dan B]

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

 

[Biff]

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

 

[wjohnson]

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.

 

[Dan B]

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