Delete Text before space if exists

[joferder]

I have a list of names that I need to remove the first name if it exists. Not all of them will have it. My list may looks like this:


John Doe
Steven Smith
Johnson

I found this formula:


=MID(A2,FIND(" ",A2)+1,256)

The problem is, that if there is no first name (so no space), it will give me a #VALUE ! error.

Any ideas of what I can use to remove just the first names if it is there would be greatly appreciated.

John

 

[MrKowz]

Try:

Excel 2013/2016
	A	B
1	John Doe	Doe
2	Steven Smith	Smith
3	Johnson	Johnson
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(RIGHT(A1,LEN(A1)-SEARCH(" ",A1)),A1)

 

[joferder]

That worked beautifully!! Thank you for the super fast reply.

John

 

[MrKowz]

That worked beautifully!! Thank you for the super fast reply.

John

Happy to help - thanks for the feedback. Have a good day!

 

[joferder]

One more question. Is there a way to make it so that it will not remove the characters before the space if there is a period in it? I have a name that is St. John as a last name, so I would need the "St." on there, but this may also apply if I have a first initial.

It's not a big thing, just kind of wondering.

John

 

[MrKowz]

One more question. Is there a way to make it so that it will not remove the characters before the space if there is a period in it? I have a name that is St. John as a last name, so I would need the "St." on there, but this may also apply if I have a first initial.

It's not a big thing, just kind of wondering.

John

Try:

Excel 2013/2016
	A	B
1	John Doe	Doe
2	Steven Smith	Smith
3	Johnson	Johnson
4	St. John	St. John
Sheet4

Cell Formulas
Range	Formula
B1	=IFERROR(IF(ISNUMBER(SEARCH(".",LEFT(A1,SEARCH(" ",A1)))),A1,RIGHT(A1,LEN(A1)-SEARCH(" ",A1))),A1)

 

[Rick Rothstein]

Is there a way to make it so that it will not remove the characters before the space if there is a period in it? I have a name that is St. John as a last name, so I would need the "St." on there, but this may also apply if I have a first initial.

Does this do what you want...

=IFERROR(IFERROR(IF(FIND(".",A1)=2,MID(A1,FIND(" ",A1)+1,99),A1),MID(A1,FIND(" ",A1)+1,99)),A1)