Extract names from cells?

[Mr2017]

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

 

[AlanY]

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

 

[JimM]

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]

=IF(ISERROR(FIND(" ",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)),RIGHT(A2,LEN(A2)-FIND(",",A2)-1))

 

[Mr2017]

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]

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]

=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]

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]

Ok, thanks for your response.