Make new column using partial text in another cell

[lmrippey]

I have a member list (210 people) where the names are all in one column
first name, last name, and "Mr." or "Ms."

My ultimate goal is to have "gender" be an added variable, get rid o
the "Mr." and "Ms.", and split the first and last names into separat
columns. If "Mr.", gender is M, if "Ms.", gender is F.

My problem is creating the gender variable. I have played with FIN
and SEARCH combined with nested IFs, but I'm having problems gettin
the formula to work for the 2nd IF. The males (M) works, but th
females (and blanks) end up a #value (see example below). Am I usin
the wrong functions?


Here is what I tried:

=IF(SEARCH("Mr.",A1),"M",IF(SEARCH("Ms.",A1),"F","XXX"))

and

=IF((SEARCH("Mr.",A1)),"M","F")

Results from both formulae:

Jones, Mr. Joe -------------M
Smith, Mr. Stan ----------- M
White, Ms. Betty ----------#VALUE!
Red, Ms. Rose -------------#VALUE!
--------------------------------#VALUE!

I appreciate any suggestions

 

[bj]

try

=IF(iserror(SEARCH("Mr.",A1)),IF(iserror(SEARCH("Ms.",A1)),"XXX","F"),"M")

Your equaiton gave an #value not a false in the if statement

 

[lmrippey]

bj,

That worked perfectly! Thank you so much