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

 

[tigeravatar]

Try Countif:
=COUNTIF(A3:U3,"*gift*")

That will return how many times gift appeared in A3:U3

 

[alex0182828]

Thanks so simple. My brain is not working that well to day.

Cheers

 

[Rick Rothstein]

Try Countif:
=COUNTIF(A3:U3,"*gift*")

That will return how many times gift appeared in A3:U3

Not exactly... what it does is return how many cells in A3:U3 contain the word "gift" which may occur one or more times within a single cell's text.

 

[alex0182828]

Thanks for the guidance on this Rick.

Alex

 

[jonchimento]

Not exactly... what it does is return how many cells in A3:U3 contain the word "gift" which may occur one or more times within a single cell's text.

I am trying to accomplish something similar but am at a loss.
I need to find the row number of a cell with text. Normally a match function would work perfectly but the text is not always the first things in the cell.
Is there a way to have excel return the row number of a cell containing text?

 

[Rick Rothstein]

I am trying to accomplish something similar but am at a loss.
I need to find the row number of a cell with text. Normally a match function would work perfectly but the text is not always the first things in the cell.
Is there a way to have excel return the row number of a cell containing text?

Use asterisk as wildcards. For example, if you were trying to match "XX" located by itself or embedded within other text, you could use something like this...

=MATCH("*XX*",A1:A999,0)

or if the text you wanted to find the text in cell B1, then this...

=MATCH("*"&B1&"*",A1:A999,0)

 

[jonchimento]

Use asterisk as wildcards. For example, if you were trying to match "XX" located by itself or embedded within other text, you could use something like this...

=MATCH("*XX*",A1:A999,0)

or if the text you wanted to find the text in cell B1, then this...

=MATCH("*"&B1&"*",A1:A999,0)

Thank you so much that worked!
I had tried:
=MATCH(B1&"*",A1:A999,0)
People said that would work but I guess I needed it on both ends.

 

[Rick Rothstein]

Thank you so much that worked!
I had tried:
=MATCH(B1&"*",A1:A999,0)
People said that would work but I guess I needed it on both ends.

If you put it on the right (like you showed), then the text would have to start with what is in B1 followed by any number of characters. If you put it on the left, then the text would have to end with what is in B1 preceded by any number of characters. With it on both sides, B1 could be anywhere with any number of characters before and/or after it. The key point is when used as a wildcard, the asterisk is a stand in for zero or more characters at the position it is in. Also note that the question mark (?) is also a wildcard... it is a stand in for a single character at its position, but that character can be anything (punctuation, letter, number).

 

[djveed]

Hey everyone. Firstly this site is so friggin awesome. I read everything and never have a need to post. Until today. Thanks for your help. Between cells B19 and J19, I've got the name of a product in B19, and several one-letter descriptors between C and J. How do I use an IF statement to list the text from B19 if the letters C, P, or F don't appear between C19 and J19? IF seems to work if I only am looking for that one letter, but for three of them?