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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Or

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 25, 2002
Messages
14
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

New Member
Joined
May 25, 2002
Messages
14

ADVERTISEMENT

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 :)
 

chunk

New Member
Joined
May 25, 2002
Messages
14
Excellent, i have just tested it, and it works a treat!

Thanks to everyone
 

Forum statistics

Threads
1,136,309
Messages
5,674,996
Members
419,541
Latest member
freddyboots

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
Top