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

chunk

New Member
Joined
May 25, 2002
Messages
14
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 :unsure: I have though, decided that maybe a macro is what I need, but I have no macro know how at all.

Please help :cry:

Thanks in advance
Paul
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
 
Upvote 0
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.:)
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,217,757
Messages
6,138,429
Members
450,136
Latest member
Tabako1960

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top