# Splitting a cell containing a name into two cells

#### fuzzyjon

##### New Member
Hello

I have a cell in my spreadsheet that contains a persons name.

I'd like to split the cell, or rather copy the firstname and surname, into two different cells. Obviously, the split point would be the space inbetween the two names but I can't work out how to do this!

I thought about using the e.g. =(LEFT A1,5) method, but the firstnames are all different lengths, so this would be no good.

Cheers,

Jon

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### xenou

##### MrExcel MVP
Hi, this seems to be most commonly posted solution to this kind of problem:

1) Select the names
2) On main menu choose Data>TextToColumns
2) Tick the box for delimited | Next | tick the box for Space | Next | Finish

That seems to do it.

EDIT: Be sure to have an empty column available to the right...or several in case there are some cells with more than one space. Check the results in case anything is split into more than two columns.

#### MickG

##### MrExcel MVP
Hi, Here's one way.
Code:
``````=+LEFT(A1,(FIND(" ",A1)))
=+RIGHT(A1,LEN(A1)-FIND(" ",A1))``````
Regards Mick

#### BAlGaInTl

##### Well-known Member
Assuming that you just have Firstname Lastname

Here is a formula for the firstname:

=LEFT(A1,FIND(" ",A1)-1)

And the Lastname

=MID(A1,FIND(" ",A1)+1,255)

#### fuzzyjon

##### New Member
Thanks everyone!!!!

Cheers,

Jon

Replies
15
Views
311
Replies
7
Views
552
Replies
5
Views
962
Replies
11
Views
260
Replies
9
Views
228

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella

### 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.

### Which adblocker are you using?

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

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