# Extract names from cells?

#### Mr2017

##### Well-known Member
Hi

I have a list of names in a column A, which I'd like to split out, so that the First Name appears in column B and the Surname appears in column C.

In the simple example below, cell A1 has the name Smith, John (with a space after the comma).

But cell A2 has the name Smith,John (without a space after the comma).

Is there a way to use one formula (which takes into account whether there's a space after the comma or not) to extract the First Name (in cells B1 and B2), then have a formula that extracts the Surname into C1 and C2?

If there isn't, I'd be grateful if anyone knows of a formula which will extract the names into columns B and C?

Please let me know if you'd like me to clarify the question.

TIA

 Smith, John Smith,John

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### AlanY

##### Well-known Member
have you tried the built-in text to columns function with , and space as delimiters?

#### JimM

##### Well-known Member
If AlanY solution doesn't work (it may struggle if you have any double barrelled names) then you could use

In colB =TRIM(LEFT(A1,FIND(",",A1)-1))
In colC =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

This does assume that there is always a comma as a separator!

#### NewOrderFac33

##### Well-known Member
=IF(ISERROR(FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)-1))

#### Mr2017

##### Well-known Member

have you tried the built-in text to columns function with , and space as delimiters?
Thanks for the suggestion, @AlanY.

I hadn't thought of that, but could consider it in future. I've used it in the past.

But I prefer to use a formula, as the name data will change in the future, so I'd rather drag a formula than go through the text to columns editor.

I appreciate the suggestion, however.

#### Mr2017

##### Well-known Member
If AlanY solution doesn't work (it may struggle if you have any double barrelled names) then you could use

In colB =TRIM(LEFT(A1,FIND(",",A1)-1))
In colC =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

This does assume that there is always a comma as a separator!

Thank you @JimM - this worked exactly as intended!

Thanks again!!

#### Mr2017

##### Well-known Member

=IF(ISERROR(FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)-1))
Hi @NewOrderFac33 - this returned the name John from cell A1, which was good (I put the formula in cell B1). However, when I dragged it down to B2, it returned 'ohn' instead of 'John.' I'm not sure, why, but I've used Jim's solution, above.

I appreciate the response, however.

Thanks

#### NewOrderFac33

##### Well-known Member
Not sure why, as it worked for me with both versions, but as long as you have something that works for you, that's the main thing.

Replies
6
Views
46
Replies
3
Views
59
Replies
15
Views
333
Replies
18
Views
555
Replies
1
Views
208

1,136,303
Messages
5,674,968
Members
419,537
Latest member
ucatchy

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