Need help with some data manipulation - to Biff
Thanks....I'm amazed! I wish I had that kind of Excel knowledge.
I did forget to mention 1 thing though....I need to split the name into 2
colums...First Name and Last Name. Would that be done with a seperate
formula afterwords, and if so, what would that be?
Thank you so much!
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
|