Searching for a partial string in a range which has complete string

Hi All,

I need to search for a partial string which is in the sheet name "Search" with the sheet name "Data". Sheet named Search has partial strings and the sheet named Data has complete string on it. For ex: if you look at A1 in search "anti Dog IgA" and search for it in sheet named data , you would get "Goat anti Dog IgA antibody" in which the partial string matches. Like this, i need to check whether all the 181 partial string is available in sheet named data or not. The result should be in sheet named Data and the result could either true or false or even if the result shows that partial string would be fine. I tried ifiserror formula but couldnt get it. Attached for your reference.


Note: I am unable to attach the file hence I am giving an example.

Sheet named Data contains:
Saravanan
Sumesh
Rajesh
Rajendran

and the sheet named Search contains :

arava
me
ale
mura
jen
jan

So, first I need to search for "arava" in the entire range in sheet named data and throw the result and move on to the next word "me" and so on.

Saravanan R

You can use wildcards with the COUNTIF function, so you could do something like this in B2 of the Search sheet:

=COUNTIF(Data!A:A,"*"&A2&"*")>0

and copy this down your list - it will return TRUE or FALSE to indicate if the partial text exists in column B of the Data sheet.

Hope this helps.

Pete

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

Hi,

Pls find the attachment.

Saravanan

You could put this formula in B2 of the Data sheet:

=SUMPRODUCT(--ISNUMBER(SEARCH(Search!$A$1:$A$182,Data!A2)))>0

then copy down - it will return TRUE or FALSE.

Hope this helps.

Pete

Hi Pete,

It is not working. For all the values the result shows as TRUE. Try from your end. Copy the string "anti Dog IgA" from search sheet and try to find in "Data" sheet. It wont be there but the formula shows as TRUE.

Saravanan

It works okay for me - the only TRUE value in column B occurs on row 5 in the Data sheet, where there is a match with:

antibody [HI186]

To test this, I put this formula in B1 of the Search sheet:

=COUNTIF(Data!$A:$A,"*"&A1&"*")

and copied down - the only non-zero values occurs in B182, and corresponds to:

antibody [HI186]

in A182. Perhaps you copied the formula incorrectly.

Hope this helps.

Pete

Hi Pete,

I tried once again but it is not coming for me. Can you please do it on an excel sheet and send back to me. Also, I tried antibody [HI186] in search sheet but I couldn't get this value.

Saravanan

File attached, as requested.

Note that I have applied conditional formatting in order to emphasise the TRUE values.

Hope this helps.

Pete

Hey Pete,

Great!!!! Thank you so much. Could you please guide me how this formula works, so I can understand about this formula.

Saravanan

The SEARCH function would normally look to see if a single text value is contained within another text value, like this:

=SEARCH("abc",A1)

and it will return a number if the text "abc" is found in A1 (the number is the character position) or it will return an error if it is not found.

In your case, you have several text values which are held in the range $A$1:$A$182 in the Search sheet. The formula will look through each of those cells in turn to see if there is a match, and so it will produce an array of results, made up of numbers or errors. The ISNUMBER term will convert these to an array of TRUE or FALSE values, and the double unary minus in front of the ISNUMBER term will convert these logical values to an array of 1s and 0s. The SUMPRODUCT term will add these up, so if the resulting sum is greater than zero then there must have been at least one substring for which there was a match. If the sum total is zero, then clearly no matches could be found.

Hope this helps.

Pete

Hi Pete,

Thanks much for the great explanation :-) Much appreciated.

Thanks once again.

Saravanan

Hi Pete,

How to change this post to solved?

Saravanan

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete