Separate text with =RIGHT(FIND) based on "space" character

[behmen]

I have a list of organisms and I'd like to split the species name from the genus. I do not want to use Text to Column bc it does not retain the full name in the original column. I tried =RIGHT(A2,FIND(" ",A2)) but it's only returning the last three letters. Do I need to specify the number of characters to return in the formula? See sample attached.

[63falcondude]

Try this in C2:

=SUBSTITUTE(A2,B2&" ","")

[mehmetcik]

Formula for B2. =LEFT(A2,FIND(".",A2))

Formula for C2 =RIGHT(A2,LEN(A2)-LEN(B2)-1)

[brant.wheeler]

behmen,

You were really close. You need to subtract the "FIND()" position from the length to get the position from the right. Here is the formula. =RIGHT(A2,LEN(A2)-FIND(" ",A2))

[behmen]

THANKS!! I need to read up on the LEN function.