I'm probably having a slow morning, so some help would be appreciated

I have a shedload of unformatted data I need to get into some order.

Firstly, I have a few thousand cells with a number, space then name, e.g.:

000205096271 Joe Bloggs

I'd like to separate the number into one cell, first name into another and surname into the third. Easy suggestions please So number would be in A1, first in A2, surname in A3 and so on.

I then need to cross-check the surnames against an existing list and flag up when known names appear in this new list; would need to do something with conditional formatting there I think.

Cheers,

Steve

Steve

Highlight the data (use Ctrl+Shift+Arrow as there's lots).

Go to Data, then text to columns, and use the delimited option. Its a bit of a wizard and will talk you though it.

Hope this helps.

Tom.

Edited 6 August, 200916 yr by Tom_vRS

I'm probably having a slow morning, so some help would be appreciated

I have a shedload of unformatted data I need to get into some order.

Firstly, I have a few thousand cells with a number, space then name, e.g.:

000205096271 Joe Bloggs

I'd like to separate the number into one cell, first name into another and surname into the third. Easy suggestions please So number would be in A1, first in A2, surname in A3 and so on.

I then need to cross-check the surnames against an existing list and flag up when known names appear in this new list; would need to do something with conditional formatting there I think.

Cheers,

Steve

A few thousand eh? - Split them all by hand in to the three different cells :P. That should keep you busy. (I mean use what Tom said above!)

Then use a good old conditional formatting - "Format only cells that contain" Then select the new list of names you are looking for.

Hope this helps,

Rob.

Thanks guys. Yes, now separated the numbers from the names, but I then can't use a fixed width separation, because people have names of different lengths

What about getting Excel to look at the first word in a cell, then copy that to another cell, then this routine again to move the surname? Possible :rubchin:

Steve

export it it to a text file then reimport it and select space for your delimiter.

Thanks guys. Yes, now separated the numbers from the names, but I then can't use a fixed width separation, because people have names of different lengths

What about getting Excel to look at the first word in a cell, then copy that to another cell, then this routine again to move the surname? Possible :rubchin:

Steve

use delimited and set to space rather than fixed?

Steve, use the delimited option, and select 'space' instead of comma etc, then the fixed length thing is irrelevant.

Steve, use the delimited option, and select 'space' instead of comma etc, then the fixed length thing is irrelevant.

what he said :

Steve

Highlight the data (use Ctrl+Shift+Arrow as there's lots).

Go to Data, then text to columns, and use the delimited option. Its a bit of a wizard and will talk you though it.

Hope this helps.

Tom.

As per post #2 :P

Told you I was having a slow day

You are forgiven Steve, just this once mind :P

Take it your sorted now?

Yeah, pretty much. It's still not ideal, but I've not been provided with the right info really

Cheers guys,

Steve.