=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe") add more like "ELB" "FLG"

[MAHMOUD]

=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe")
HOW TO ADD MORE PARTIAL TEXT LIKE "ELB" "FLG" OR MORE PARTIAL TEXT TO THE
SAME FUNCTION

 

[Ashish Mathur]

Hi,

Array enter the following formula (Ctrl+Shift+Enter). B9:B10 holds your
partial text values

=IF(OR(ISNUMBER(SEARCH(B9:B10,H5))),"Pipe")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[JP Ronse]

Hi Mahmoud,

If you don't like to put the partial text in a range, you can use:

=IF(OR(ISNUMBER(SEARCH("elb",B3)),ISNUMBER(SEARCH("flg",B3))),"pipe")

If you have a lot of partial text to find then my formula becomes fast too
long and obscure. In that case you better gpo for Ashish' approach.

Wkr,

JP

 

[toto]

Hi,

Array enter the following formula (Ctrl+Shift+Enter).  B9:B10 holds your
partial text values

=IF(OR(ISNUMBER(SEARCH(B9:B10,H5))),"Pipe")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com






- Show quoted text -

Dear Tim,
The formula as below is describes a situation which I
want to search for the written letter ″ELB″ ,the below formula
succeeded to find the letter ″ELB″ and print the answer word "Pipe",
which I need to classify the word ″ELB″ underneath in the last column
named results ,in case if there is no letter ″ELB″ therefore the
formula results is =False in the same column of results .
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe")

What I need now is to catch more letter plus the letters of ″ELB″ like
the letters of ″FLG″ for example or more if required, please advice.
Material M. description Results
1000787665 1" CAP FALSE
1000787987 1" CAP FALSE
1000788420 1" ELB. 10 Pipe
1000788419 1" ELB. 7 Pipe
1000788378 1" ELB.77 Pipe
1000787689 1" ELB.45 Pipe
1000787976 1" ELB.458 Pipe
1000787622 1" ELB.90 8 Pipe
1000788072 1" FLG BL FALSE
1000787024 1" FLG WN 8. FALSE
1000798135 1" FLG WN 8 FALSE
1000786967 1" FLG WN 447. FALSE
1000788175 1" FLG WN44 FALSE
1000786940 1" FLG. WN.. FALSE
1000788230 1" FLG. WN. FALSE
1000788248 1" FLG. WN. RF FALSE
1000788192 1" FLG. WN. T FALSE
1000786956 1" FLG. WN. 2. FALSE
1000788262 1" FLG. WN. R8 FALSE
1000798962 1" GASKET FALSE
1000786754 1" NIPOLET FALSE

 

[toto]

Hi,

Array enter the following formula (Ctrl+Shift+Enter).  B9:B10 holds your
partial text values

=IF(OR(ISNUMBER(SEARCH(B9:B10,H5))),"Pipe")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com






- Show quoted text -

Dear JP Ronse ,
The formula as below is describes a situation
which I want to search for the written letter ″ELB″ ,the below formula
succeeded to find the letter ″ELB″ and print the answer word "Pipe",
which I need to classify the word ″ELB″ underneath in the last column
named results ,in case if there is no letter ″ELB″ therefore the
formula results is =False in the same column of results .
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe")

What I need now is to catch more letter plus the letters of ″ELB″ like
the letters of ″FLG″ for example or more if required, please advice.

** I am sorry , I tried the range as you recommended ,but it was not
succeed ,because when I repeat the formula in the next row cell ,he
change the cells to the next one and I will add more classifications
categories not only the PIPE ,please advice.

Material M. description Results
1000787665 1" CAP FALSE
1000787987 1" CAP FALSE
1000788420 1" ELB. 10 Pipe
1000788419 1" ELB. 7 Pipe
1000788378 1" ELB.77 Pipe
1000787689 1" ELB.45 Pipe
1000787976 1" ELB.458 Pipe
1000787622 1" ELB.90 8 Pipe
1000788072 1" FLG BL FALSE
1000787024 1" FLG WN 8. FALSE
1000798135 1" FLG WN 8 FALSE
1000786967 1" FLG WN 447. FALSE
1000788175 1" FLG WN44 FALSE
1000788160 1" FLG. 44 FALSE
1000786940 1" FLG. WN.. FALSE
1000788230 1" FLG. WN. FALSE
1000788248 1" FLG. WN. RF FALSE
1000788192 1" FLG. WN. T FALSE
1000786956 1" FLG. WN. 2. FALSE
1000788262 1" FLG. WN. R8 FALSE
1000798962 1" GASKET FALSE
1000786754 1" NIPOLET FALSE

 

[T. Valko]

Make a list of the items you want to search for:

...........G..........
1......cap.........
2......elb.........
3......pipe.......
4......flg..........
5.....gasket.....

Then:

B3 = 1000787024 1" flg WN 8

=LOOKUP(1E100,SEARCH(G1:G5,B3),G1:G5)

Result = flg

--
Biff
Microsoft Excel MVP

Hi,

Array enter the following formula (Ctrl+Shift+Enter). B9:B10 holds your
partial text values

=IF(OR(ISNUMBER(SEARCH(B9:B10,H5))),"Pipe")

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com






- Show quoted text -

Dear JP Ronse ,
The formula as below is describes a situation
which I want to search for the written letter ?ELB? ,the below formula
succeeded to find the letter ?ELB? and print the answer word "Pipe",
which I need to classify the word ?ELB? underneath in the last column
named results ,in case if there is no letter ?ELB? therefore the
formula results is =False in the same column of results .
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe")

What I need now is to catch more letter plus the letters of ?ELB? like
the letters of ?FLG? for example or more if required, please advice.

** I am sorry , I tried the range as you recommended ,but it was not
succeed ,because when I repeat the formula in the next row cell ,he
change the cells to the next one and I will add more classifications
categories not only the PIPE ,please advice.

Material M. description Results
1000787665 1" CAP FALSE
1000787987 1" CAP FALSE
1000788420 1" ELB. 10 Pipe
1000788419 1" ELB. 7 Pipe
1000788378 1" ELB.77 Pipe
1000787689 1" ELB.45 Pipe
1000787976 1" ELB.458 Pipe
1000787622 1" ELB.90 8 Pipe
1000788072 1" FLG BL FALSE
1000787024 1" FLG WN 8. FALSE
1000798135 1" FLG WN 8 FALSE
1000786967 1" FLG WN 447. FALSE
1000788175 1" FLG WN44 FALSE
1000788160 1" FLG. 44 FALSE
1000786940 1" FLG. WN.. FALSE
1000788230 1" FLG. WN. FALSE
1000788248 1" FLG. WN. RF FALSE
1000788192 1" FLG. WN. T FALSE
1000786956 1" FLG. WN. 2. FALSE
1000788262 1" FLG. WN. R8 FALSE
1000798962 1" GASKET FALSE
1000786754 1" NIPOLET FALSE