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 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!

[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" 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
>
>

[lmrippey]

bj,

That worked perfectly! Thank you so much!