Make new column using partial text in another cell
 

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 of
the "Mr." and "Ms.", and split the first and last names into separate
columns. If "Mr.", gender is M, if "Ms.", gender is F.

My problem is creating the gender variable. I have played with FIND
and SEARCH combined with nested IFs, but I'm having problems getting
the formula to work for the 2nd IF. The males (M) works, but the
females (and blanks) end up a #value (see example below). Am I using
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!


--
lmrippey
------------------------------------------------------------------------
lmrippey's Profile: http://www.excelforum.com/member.php...fo&userid=2940
View this thread: http://www.excelforum.com/showthread...hreadid=471897

try

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

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

"lmrippey" wrote:


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 of
the "Mr." and "Ms.", and split the first and last names into separate
columns. If "Mr.", gender is M, if "Ms.", gender is F.

My problem is creating the gender variable. I have played with FIND
and SEARCH combined with nested IFs, but I'm having problems getting
the formula to work for the 2nd IF. The males (M) works, but the
females (and blanks) end up a #value (see example below). Am I using
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!


--
lmrippey
------------------------------------------------------------------------
lmrippey's Profile: http://www.excelforum.com/member.php...fo&userid=2940
View this thread: http://www.excelforum.com/showthread...hreadid=471897

bj,

That worked perfectly! Thank you so much!


--
lmrippey
------------------------------------------------------------------------
lmrippey's Profile: http://www.excelforum.com/member.php...fo&userid=2940
View this thread: http://www.excelforum.com/showthread...hreadid=471897