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
 

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
Joined
Oct 30, 2014
Messages
4,243
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
736
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,255
=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,255
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.
 

Forum statistics

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