Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with some data manipulation | Excel Worksheet Functions | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |