Find text and enter in new cell
 

Text in column A cells may contain 1 of the following: alpha, beta, gamma,
delta.

I want to search cells in column A for all of these values and, if one is
found, insert that value in the same cell of column B.

For example, cell A3 contains beta, kappa, and tau. I want to check for
alpha, beta, gamma, or delta. The search should determine that beta is
present and then place beta in cell B3.

If the search does not find alpha, beta, gamma, or delta, then "other"
should be placed in the corresponding cell of column B.

Is this possible?

Thanks,
Tom

Find text and enter in new cell
 

=if(countif(a1,"*alpha*")0,"alpha",if(countif(a1, "*beta*")0,"beta",if(countif(a1,"*gamma*")0,"gam ma",if(countif(a1,"*delta*")0,"delta","other")) ))
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"tommcbrny" wrote:

Text in column A cells may contain 1 of the following: alpha, beta, gamma,
delta.

I want to search cells in column A for all of these values and, if one is
found, insert that value in the same cell of column B.

For example, cell A3 contains beta, kappa, and tau. I want to check for
alpha, beta, gamma, or delta. The search should determine that beta is
present and then place beta in cell B3.

If the search does not find alpha, beta, gamma, or delta, then "other"
should be placed in the corresponding cell of column B.

Is this possible?

Thanks,
Tom

Find text and enter in new cell
 

Awesome, thank you!

"KC Rippstein" wrote:

=if(countif(a1,"*alpha*")0,"alpha",if(countif(a1, "*beta*")0,"beta",if(countif(a1,"*gamma*")0,"gam ma",if(countif(a1,"*delta*")0,"delta","other")) ))
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"tommcbrny" wrote:

Text in column A cells may contain 1 of the following: alpha, beta, gamma,
delta.

I want to search cells in column A for all of these values and, if one is
found, insert that value in the same cell of column B.

For example, cell A3 contains beta, kappa, and tau. I want to check for
alpha, beta, gamma, or delta. The search should determine that beta is
present and then place beta in cell B3.

If the search does not find alpha, beta, gamma, or delta, then "other"
should be placed in the corresponding cell of column B.

Is this possible?

Thanks,
Tom

Find text and enter in new cell
 

Folllow-up question to this, although the solution works quite nicely:
Is the number of iterations of the if statements limited?
Thanks again
Tom

"KC Rippstein" wrote:

=if(countif(a1,"*alpha*")0,"alpha",if(countif(a1, "*beta*")0,"beta",if(countif(a1,"*gamma*")0,"gam ma",if(countif(a1,"*delta*")0,"delta","other")) ))
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"tommcbrny" wrote:

Text in column A cells may contain 1 of the following: alpha, beta, gamma,
delta.

I want to search cells in column A for all of these values and, if one is
found, insert that value in the same cell of column B.

For example, cell A3 contains beta, kappa, and tau. I want to check for
alpha, beta, gamma, or delta. The search should determine that beta is
present and then place beta in cell B3.

If the search does not find alpha, beta, gamma, or delta, then "other"
should be placed in the corresponding cell of column B.

Is this possible?

Thanks,
Tom

Find text and enter in new cell
 

On Fri, 9 May 2008 08:31:02 -0700, tommcbrny
wrote:

Text in column A cells may contain 1 of the following: alpha, beta, gamma,
delta.

I want to search cells in column A for all of these values and, if one is
found, insert that value in the same cell of column B.

For example, cell A3 contains beta, kappa, and tau. I want to check for
alpha, beta, gamma, or delta. The search should determine that beta is
present and then place beta in cell B3.

If the search does not find alpha, beta, gamma, or delta, then "other"
should be placed in the corresponding cell of column B.

Is this possible?

Thanks,
Tom

Enter your allowed words in some column, one word to a cell, and name that
range "List".

Then enter this "array-entered" formula in B1, and fill down as far as
required:

=IF(SUM(-ISNUMBER(SEARCH(List,A1)))=0,"Other",
INDEX(List,MATCH(TRUE,ISNUMBER(SEARCH(List,A1)),0) ))

To **array-enter** a formula, after typing or pasting the formula into the
formula bar, confirm it by holding down <ctrl<shift while you hit <enter.
Excel will place braces {...} around the formula if you did it correctly.

List can be any size, so long as it is in a single column.
--ron