Help Needed: Move certain text at the end to the begining

[chunk]

Hi Peeps,

I really need some help on a task I am trying to do on data I have collected in excel.

SMITH, JOHN ----> JOHN SMITH

Above is an example on what I am trying to do in a column (C) of data in excel (2003). I have a list of over 800 records, and I want the first name to be moved to the front on all the records in the column, plus the ‘,’ to be deleted and any spaces removed because of the cutting and pasting. This is so I can create a mail shot to my customers.
I have been racking my brain and it’s got to the stage where I have a mental block I have though, decided that maybe a macro is what I need, but I have no macro know how at all.

Please help

Thanks in advance
Paul

 

[NBVC]

Here's one way, assuming all names are as per example with no middle initials or middle names:

=MID(A2,FIND(", ",A2)+2,LEN(A2))&" "&LEFT(A2,FIND(",",A2)-1)

where A2 houses a name to convert

 

[texasalynn]

the longer method you could do text to column -- deliminted with ","
then you can put the two columns back together

=A1 &" "&B1
and copy the formula down

 

[VoG]

Or

=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)& " " & LEFT(A1,FIND(",",A1)-1)

 

[Norie]

Paul

Another option would be to do the Text to Columns as texasalynn suggested.

Then just leave the first name and last name in seperate columns.

You could then use the 2 different fields in your mail merge.

PS I'm assuming you're using mail merge.

 

[chunk]

Wow thanks for the quick replies.

Some records do have middle initials which could look like ‘SMITH, JOHN M’, but basically anything after the ',' is to be cut and pasted to the beginning

Will any of the functions posted do that?

 

[chunk]

Paul

Another option would be to do the Text to Columns as texasalynn suggested.

Then just leave the first name and last name in seperate columns.

You could then use the 2 different fields in your mail merge.

PS I'm assuming you're using mail merge.

Yep thats what im planning to do

 

[texasalynn]

any of them will do that because the key is the comma

 

[chunk]

Excellent, i have just tested it, and it works a treat!

Thanks to everyone