Search text Between a range

[nittin123goel]

Hi Guys,

Need to search a single word in a column range.
For example I want to search "apple" In my attached sheet.

"apple" may lie in any column.
I want as I search "apple" any column range containing the same word got filter.

Thanks in advance

Cheers

[Ron Coderre]

I'd recommend using a helper column in your posted workbook.

G1: Flag
G2: =COUNTIF(B2:F2,"*apple*")>0

Copy that formula down through G13

To only view rows where at least one of the cells contains: Apple
...Filter on the Flag column for the value: TRUE.

Is that something you can work with?

[bstoyano]

What I would suggest is that you write an expression in a new column, that should look sth like "=FIND("apple";CONCATENATE(A1;A2;A3...))" or within the whole row. Then apply the filter to this new column.
FIND will return as integer with the starting position. if it does not find the string, it will return #value, and you can use the functions to handle this error in a user-friendly manner.

[nittin123goel]

Hey Ron,

yes Its working But there is problem in case if its written "apples" then it shows the result FALSE. Help me with that also.

I'd recommend using a helper column in your posted workbook.

G1: Flag
G2: =COUNTIF(B2:F2,"*apple*")>0

Copy that formula down through G13

To only view rows where at least one of the cells contains: Apple
...Filter on the Flag column for the value: TRUE.

Is that something you can work with?

[Ron Coderre]

Hey Ron,

yes Its working But there is problem in case if its written "apples" then it shows the result FALSE. Help me with that also.

I'm not seeing that issue. If I change cell E4 from "all pharma" to "all apples pharma"...the formula result for that row (G4) changes from FALSE to TRUE.
Can you post a specific example of the problem?