Re: Need help with some data manipulation - to Biff

[Dan B]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Dan B]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>