Find text and enter in new cell

  • Thread starter Thread starter tommcbrny
  • Start date Start date
T

tommcbrny

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
 
=if(countif(a1,"*alpha*")>0,"alpha",if(countif(a1,"*beta*")>0,"beta",if(countif(a1,"*gamma*")>0,"gamma",if(countif(a1,"*delta*")>0,"delta","other"))))
 
Awesome, thank you!

KC Rippstein said:
=if(countif(a1,"*alpha*")>0,"alpha",if(countif(a1,"*beta*")>0,"beta",if(countif(a1,"*gamma*")>0,"gamma",if(countif(a1,"*delta*")>0,"delta","other"))))
Click to expand...
 
Folllow-up question to this, although the solution works quite nicely:
Is the number of iterations of the if statements limited?
Thanks again
Tom
 
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
Click to expand...

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
 
Back
Top