Check if one cell contains partial text from another cell and return 'Yes' or No'

[Frank_Kruyer]

Hi,

This is my first time posting. I am not an Excel expert but feel like the answer to my question will be solved by an array formula, an area which I need to improve!

Anyway here goes...

I have a list in Sheet 1, Column A, of cells containing text:

<table border="0" cellpadding="0" cellspacing="0" width="209"><colgroup><col style="mso-width-source:userset;mso-width-alt:7643;width:157pt" width="209"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:157pt" height="17" width="209">Text 1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 2</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 3</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 4</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 5</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 6</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 7</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 8</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Text 9</td> </tr> </tbody></table>
On Sheet 2, I have another list containg text, obtained from an external data source. However due to the format of the source all text is in Upper case and void of punctuation. For example if Sheet 1 contains a cell with the text 'Don't Go', in Sheet 2 it will appear 'DON T GO'.

In another column on Sheet 1 I have been using a simple vlookup with an if and iserr statement to return a value of either 'Y' or 'N'. So basically I am checking to see if the title from Sheet 1, appears in Sheet 2:

=IF(ISERROR(VLOOKUP(A5,Sheet 2!$B$2:$G$12,1,0)),"N","Y")

This is fine as long I am looking up text that does not contain punctuations, when punctuations are present the value my formula returns is not true.

So I need a formula, ideally a lookup, which will look at the cell in Sheet 1, and check Sheet 2 for a cell containing that text. The formula above with an approximate match will not suffice as there are too many values in Sheet 2 which will false return a 'Y' result.

So I'm stumped, obviously I could just format the text in either sheet to compensate for punctuation (either inserting or removing it) but with 100's of rows this could be very time consuming. Also, this kind of manual find and replace work means there is more room for human error. If anyone feel they can help (you may need more info / to see an example sheet) that would be amazing.

Apologies for the lengthy post!

 

[Gerald Higgins]

Well, Find and Replace might be OK, if you don't have too many steps.

You can convert all the text to upper case by using the =UPPER() function. There are other case formating functions that you could look at, but UPPER might be best here.

You could then find and replace all the " ' " characters with a space, if that's what you want to do.
Then work out what other punctuation characters you have, if any, and find and replace them too.

ps - welcome to the board !