Split Lastname, FirstName into two cells

cashew1970

Board Regular
Joined
Feb 28, 2005
Messages
57
Hello All,

I have a DB and the name information is delivered as LastName, FirstName in cell B

Ideally I need to split this so that The Firstname only is in cell F and the Last Name only is in Cell G

Is there an easy way for me to do this??

Cheers

Martin
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi there

you can do this via

Data > Text to Columns > Fixed with > Next > "place a line between the names" > next > finish

Mark
 
Upvote 0
Is there any way to do this as a forula? The text to cols works OK, but I have some idiot users that nedd to be able to just dump information into the sheet, and then be able to mail merge straight from it....

If I have to get them to play afetr the datadump... I am sure things will go wrong
 
Upvote 0
Martin,

If you are continually adding names to your database, and do not want to use the Text-to-Columns method after each entry, you can also insert the following formulas...

Assuming your data (LastName, FirstName) starts in cell B2.

In Column F enter: =TRIM(RIGHT(B2,LEN(B2)-SEARCH(",",B2)-1))
Then copy down the column

In Column G enter: =LEFT(B2,FIND(",",B2)-1)
Then copy down the column

Good luck,
Jim
 
Upvote 0
cashew1970

Try this:
F2 (copied down): =REPLACE(B2,1,FIND(",",B2)+1,"")
G2 (copied down): =SUBSTITUTE(B2,", "&F2,"")
Mr Excel.xls
ABCDEFGH
1Last, FirstFirstLast
2Smith, JohnJohnSmith
3Brown, TedTedBrown
4
Reverse Names
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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