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 |
Need help with some data manipulation - to Biff
And another thing related to last part of your first post....each group does
not have the same number of rows. Some have 4 rows some have 8. And yes, there is a blank row between each group. "Dan B" wrote in message ... 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 |
All times are GMT +1. The time now is 09:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com