Extract names from cells?

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,203
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
have you tried the built-in text to columns function with , and space as delimiters?
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
682
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
Joined
Sep 26, 2011
Messages
1,252
=IF(ISERROR(FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)-1))
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

=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
Joined
Sep 26, 2011
Messages
1,252
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

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok, thanks for your response.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,879
Members
410,643
Latest member
sng
Top