Within a single column I would like to identify whether four possible strings
exists and then list a corresponding number in the formula column.

In the past I have used something like this:

=IF(I2gt;=quot;MILLERquot;,1) IF(I2gt;=quot;DAVISquot;,2) IF(I2gt;=quot;SMIT Hquot;,3) IF(I2gt;=quot;WINTERquot;,4)

And while this works, sort of... it is inelegant at best. Excel acutally
adds the numbers together or something and I end up with corresponding
numbers like, 10, 8, 6, and 4.

Further, I would like to search a long string for the word quot;DAVISquot; and not
have to list the entire beginning of the string.

Try something like this if your list of search names isn't too long:
=SUMPRODUCT(COUNTIF(A1,{quot;*miller*quot;,quot;*davis*quot;,quot;*smi th*quot;} )*{1,2,3})

Notes:
1)No error checking necessary since the COUNTIF will return zero if no match.
2)The fomrula won't work if the cell contains multiple searched values.
example: A1: Mr. Miller Davis Smith

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Nicole Seibertquot; wrote:

gt; Within a single column I would like to identify whether four possible strings
gt; exists and then list a corresponding number in the formula column.
gt;
gt; In the past I have used something like this:
gt;
gt; =IF(I2gt;=quot;MILLERquot;,1) IF(I2gt;=quot;DAVISquot;,2) IF(I2gt;=quot;SMIT Hquot;,3) IF(I2gt;=quot;WINTERquot;,4)
gt;
gt; And while this works, sort of... it is inelegant at best. Excel acutally
gt; adds the numbers together or something and I end up with corresponding
gt; numbers like, 10, 8, 6, and 4.
gt;
gt; Further, I would like to search a long string for the word quot;DAVISquot; and not
gt; have to list the entire beginning of the string.

Thank you so much! You have been a great help. I would never have thought
to work with mathmatical formula.quot;Nicole Seibertquot; wrote:

gt; Within a single column I would like to identify whether four possible strings
gt; exists and then list a corresponding number in the formula column.
gt;
gt; In the past I have used something like this:
gt;
gt; =IF(I2gt;=quot;MILLERquot;,1) IF(I2gt;=quot;DAVISquot;,2) IF(I2gt;=quot;SMIT Hquot;,3) IF(I2gt;=quot;WINTERquot;,4)
gt;
gt; And while this works, sort of... it is inelegant at best. Excel acutally
gt; adds the numbers together or something and I end up with corresponding
gt; numbers like, 10, 8, 6, and 4.
gt;
gt; Further, I would like to search a long string for the word quot;DAVISquot; and not
gt; have to list the entire beginning of the string.

You're very welcome.
I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Proquot;Nicole Seibertquot; wrote:

gt; Thank you so much! You have been a great help. I would never have thought
gt; to work with mathmatical formula.
gt;
gt;
gt; quot;Nicole Seibertquot; wrote:
gt;
gt; gt; Within a single column I would like to identify whether four possible strings
gt; gt; exists and then list a corresponding number in the formula column.
gt; gt;
gt; gt; In the past I have used something like this:
gt; gt;
gt; gt; =IF(I2gt;=quot;MILLERquot;,1) IF(I2gt;=quot;DAVISquot;,2) IF(I2gt;=quot;SMIT Hquot;,3) IF(I2gt;=quot;WINTERquot;,4)
gt; gt;
gt; gt; And while this works, sort of... it is inelegant at best. Excel acutally
gt; gt; adds the numbers together or something and I end up with corresponding
gt; gt; numbers like, 10, 8, 6, and 4.
gt; gt;
gt; gt; Further, I would like to search a long string for the word quot;DAVISquot; and not
gt; gt; have to list the entire beginning of the string.