# 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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### AlanY

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

• Mr2017

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

• Mr2017

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

• Mr2017

Replies
4
Views
120
Replies
3
Views
110
Replies
1
Views
46
Replies
4
Views
53
Replies
6
Views
68