Find text string Across Multiple Cells in a Row

[alex0182828]

Hi,

I want to see if a text string is contained in any the text in any cell in a row i tried :

=FIND("gift",A3:U3,1)

Did not work. I tried defining a name for the range and putting it where there range is in the formula, also did not work.

Any ideas for doing this in a formula ? I feel like it should be so simple !

Thanks. All

 

[Rick Rothstein]

I think this formula does what you want...

=IF(ISNUMBER(SEARCH({"C","F","P"},C19&D19&E19&F19&G19&H19&I19*J19)),"",B19)

 

[djveed]

Thanks so much. This doesn't seem to work. No matter what is populated in cells C through J, this always provides the string from B. This should provide the string only if NONE of C, F, or P appears in C-J.

I think this formula does what you want...

=IF(ISNUMBER(SEARCH({"C","F","P"},C19&D19&E19&F19&G19&H19&I19*J19)),"",B19)

 

[Rick Rothstein]

Thanks so much. This doesn't seem to work. No matter what is populated in cells C through J, this always provides the string from B. This should provide the string only if NONE of C, F, or P appears in C-J.

I must not be understanding what you mean by "NONE of C, F, or P"... my formula assumed you meant that you wanted to see the contents of B19 as long as C19:J19 did not contain a C or an F or a P anywhere in the range. In other words, if C19:J19 looked something like this...

A-B-D-E-G-H-I-J

then show B19, but if it looked like this...

A-B-C-E-G-H-I-J

or this...

A-B-D-E-F-H-I-J

or like this...

A-B-D-E-G-H-I-P

or like this...

A-C-D-F-G-H-I-P

etc., then do not show B19. Can you show us, using similar examples, what you actually wanted?

 

[djveed]

This is exactly right.

I must not be understanding what you mean by "NONE of C, F, or P"... my formula assumed you meant that you wanted to see the contents of B19 as long as C19:J19 did not contain a C or an F or a P anywhere in the range. In other words, if C19:J19 looked something like this...

A-B-D-E-G-H-I-J

then show B19, but if it looked like this...

A-B-C-E-G-H-I-J

or this...

A-B-D-E-F-H-I-J

or like this...

A-B-D-E-G-H-I-P

or like this...

A-C-D-F-G-H-I-P

etc., then do not show B19. Can you show us, using similar examples, what you actually wanted?

 

[Rick Rothstein]

This is exactly right.

Sorry, I see now that I posted the wrong formula. Try this one instead...

=IF(SUM(0+ISNUMBER(SEARCH({"C","F","P"},C19&D19&E19&F19&G19&H19&I19&J19))),"",B19)

 

[number_cruncher]

Hello. I am new to this group so please let me know if I have posted in the wrong place.

Each row of my report contains a plethora of contact info for each person including their business contact info (H:L). Although all business data is always in cells H:L, it's not always entered the same way.

IF $H$2:$L$2 contains the text "McDonalds" AND the text "Miami", I want the result listed in $M$2 - "Miami McDonalds Empoloyee" if true, "" if false.

	A	B	H	I	J	K	L	M
1	First	Last	BUS_1	BUS_2	BUS_3	BUS-4	BUS-5
2	Mary	Jane	McDonalds	123 Park St., Miami, FL	Cashier
3	Amy	Lou	Burger King	Manager	889 Fast Lane	Bolder, Colorado 99869
4	John	Bolder	Assistant Manager	Corporate Store - McDonalds	Reports to Lary Johnson	323 5th St. West	Miami, FL 12345
5	Karen	Long	Line Cook	Franchise - Burger King	Franchise Store	9263 Timbucktoo, Salt Lake City, UT 99887

<colgroup><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thank you for your help!

 

[jonchimento]

This equation works: =IF(AND(ISNUMBER(COUNTIF(H2:L2,"*McDonalds*")),ISNUMBER(COUNTIF(H2:L2,"*Miami*"))),"Miami McDonalds Employee")
You can ask questions the way you did in an existing thread or start a new thread.

Hello. I am new to this group so please let me know if I have posted in the wrong place.

Each row of my report contains a plethora of contact info for each person including their business contact info (H:L). Although all business data is always in cells H:L, it's not always entered the same way.

IF $H$2:$L$2 contains the text "McDonalds" AND the text "Miami", I want the result listed in $M$2 - "Miami McDonalds Empoloyee" if true, "" if false.

	A	B	H	I	J	K	L	M
1	First	Last	BUS_1	BUS_2	BUS_3	BUS-4	BUS-5
2	Mary	Jane	McDonalds	123 Park St., Miami, FL	Cashier
3	Amy	Lou	Burger King	Manager	889 Fast Lane	Bolder, Colorado 99869
4	John	Bolder	Assistant Manager	Corporate Store - McDonalds	Reports to Lary Johnson	323 5th St. West	Miami, FL 12345
5	Karen	Long	Line Cook	Franchise - Burger King	Franchise Store	9263 Timbucktoo, Salt Lake City, UT 99887

<tbody>
</tbody>

Thank you for your help!

 

[number_cruncher]

Thank you johnchimento. This was a great start. When I copied the formula down the row, it told me that everyone was a "Miami McDonalds Employee". I played with relative and absolute references but it did not make a difference.

I have also since discovered that I need to include an OR formula as well. I've attempted to add it, but it doesn't seem to be working. I need find BOTH phrases, "Miami" and "McDonalds" in H2:L2 OR AB:AH. It cannot be "Miami" found in H2:L2 and "McDonalds" found in AB:AH. If result is not found, it's ok for #N/A to be displayed.

This equation works: =IF(AND(ISNUMBER(COUNTIF(H2:L2,"*McDonalds*")),ISNUMBER(COUNTIF(H2:L2,"*Miami*"))),"Miami McDonalds Employee")
You can ask questions the way you did in an existing thread or start a new thread.

 

[jonchimento]

This will give you a FALSE result if it doesn't meet the criteria, should work now:
=IF(AND(ISNUMBER(IF(COUNTIF(H2:L2,"*McDonalds*")>0,1)),ISNUMBER(IF(COUNTIF(H2:L2,"*Miami*")>0,1))),"Miami McDonalds Employee")

Thank you johnchimento. This was a great start. When I copied the formula down the row, it told me that everyone was a "Miami McDonalds Employee". I played with relative and absolute references but it did not make a difference.

I have also since discovered that I need to include an OR formula as well. I've attempted to add it, but it doesn't seem to be working. I need find BOTH phrases, "Miami" and "McDonalds" in H2:L2 OR AB:AH. It cannot be "Miami" found in H2:L2 and "McDonalds" found in AB:AH. If result is not found, it's ok for #N/A to be displayed.