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

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi there

you can do this via

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

Mark
 

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
I'd delimit it by a comma, if there is a comma between LastName, FirstName
 

cashew1970

Board Regular
Joined
Feb 28, 2005
Messages
57

ADVERTISEMENT

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
 

Jim-in-I

Board Regular
Joined
Sep 10, 2002
Messages
180
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,670
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,756
Messages
5,708,333
Members
421,566
Latest member
7Nabisco

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