Using IF(ISNUMBER(SEARCH

[mfabri]

Hi Guys,

I'm using the IF(ISNUMBER(SEARCH formula set to find a especific number in a excel file

=IF(ISNUMBER(SEARCH(E3,$D$2:$D$48)), E3, "NO")

Basicaly I have an array of numbers in E and a combination of words and numbers in D I need to know if the number in E is anywhere in D

For some reason the formula works with just one ROW but not the combination of 48 ROWS

Can someone help?

Thanks a lot!

 

[mfabri]

and my long formula
=SUMPRODUCT((--(ISNUMBER(SEARCH(E3,$D$2:$D$48)))*ROW($D$2:$D$48)))

Folks another question...

My data sets look like this...

The number always have a combination of 7 digits: 1234567

The combination of Letters and numbers is: Hello my name is david I'm H1234567 and G123456789 but maybe I'm I12345

I need to find which set of 7 digits number is in the set of letters and numbers. How can I isolate the number from the data set? I mean, the number I want could be anywhere in the sentence, could have or not the H in front of it, the only thing for sure is that the number I want will be the only one with 7 digits in the sentence.

Its possible to create a formula to isolate the only number in the sentance with 7 digits?

 

[admiral100]

maybe this....
K9 is the cell with the long text
E3 is contain 1234567
=MID(K9,MIN(FIND(E3,K9&"0123456789")),7)