problem in finding words into cells of a column

C

Claudio P.

My formula can find only the word cremona into from cell D2 to cell
D100.
can you help me please ?

--------------CUTHERE----------------
=IF(FIND("cremona";D2)>0;"Cremona";IF(FIND("spatocco";D2)>0;"spatocco";IF(FIND("magliano";D2)>0;"magliano";IF(FIND("desenzano";D2)>0;"desenzano";IF(FIND("aosta";D2)>0;"aosta";IF(FIND("ascoli";D2)>0;"ascoli";IF(FIND("amandola";D2)>0;"amandola";"")))))))
--------------CUTHERE----------------

thank you very much

Claudio
 
B

bob777

Are you trying to count the number of times cremona etc appears, or to
put the word cremona in another cell, or to highlight the cell? Is your
data single town names , or town names inside a set of other words?
 
M

Max

Perhaps try replacing FIND with SEARCH in your formula.
FIND is case-sensitive while SEARCH is not

Another way to try ..

Let's say the names: Cremona, Spatacco, etc are listed down in X1:X20

Then we could put in say, E2's formula bar
and array-enter the formula (i.e. press CTRL+SHIFT+ENTER):
=IF(D2="","",INDEX($K$1:$K$20,MATCH(1,ISNUMBER(SEARCH($K$1:$K$20,D2))*($K$1:
$K$20<>""),0)))
and copy E2 down as far as required

This may accomplish the same results in a slightly neater way
(if I've read your intent correctly)

Note: You'd need to change the commas in the formula
to semicolons ";" to suit your Excel language
 
Top