• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to list of cell values that contain or equal to given data.

Peter Bartholomew

Well-Known Member
If a list of TRUE/FALSE will do, you could use
Code:
= ISNUMBER(SEARCH(code, sourceData))
If required, the returned values could be used to filter the source data to show only matches.
 

bosco_yip

Excel Ninja
A similar idea as compare to Peter's solution,

Just add space " " in front of And behind the Entry code and the Source data,

Which, consider if the Entry code put in front of (as per image C8) or behind the Source data (as per image C7),

something like this.

In B3, formula copied down :

=ISNUMBER(SEARCH(" "&A$2&" "," "&C3&" "))

80309
 

arif120

New Member
A similar idea as compare to Peter's solution,

Just add space " " in front of And behind the Entry code and the Source data,

Which, consider if the Entry code put in front of (as per image C8) or behind the Source data (as per image C7),

something like this.

In B3, formula copied down :

=ISNUMBER(SEARCH(" "&A$2&" "," "&C3&" "))

View attachment 80309
Thank you very much for the reply.
Basically I wanted output with non blank match value.
 

Attachments

Peter Bartholomew

Well-Known Member
A number of presentation strategies are possible.
Code:
Condition
= ISNUMBER(SEARCH(code, sourceData))

Showing matches within adjacent column
= IF(ISNUMBER(SEARCH(code, sourceData)), sourceData)

Showing matches as a filtered list
= FILTER( sourceData, ISNUMBER(SEARCH(code, sourceData))

Reducing to {0,1} for display using number format symbols
= N(ISNUMBER(SEARCH(code, sourceData)))
As an aside, the padding with spaces can be useful to prevent a code being wrongly matched to part of a longer text string.
 

arif120

New Member
View attachment 80311
A number of presentation strategies are possible.
Code:
Condition
= ISNUMBER(SEARCH(code, sourceData))

Showing matches within adjacent column
= IF(ISNUMBER(SEARCH(code, sourceData)), sourceData)

Showing matches as a filtered list
= FILTER( sourceData, ISNUMBER(SEARCH(code, sourceData))

Reducing to {0,1} for display using number format symbols
= N(ISNUMBER(SEARCH(code, sourceData)))
As an aside, the padding with spaces can be useful to prevent a code being wrongly matched to part of a longer text string.
Thanks a lot. You are a brilliant.
That's exactly what I wanted. Problem solved.
 
Top