how to get Excel to ignore forward slashes and diacritic marks when finding matches

[anita2017]

Dear Excel Community,

I have two columns that have at least 30 rows but for brevity, the excel sheet attached for your review only has 6 rows of data. Column A contains the correct phonetic sounds that students are expected to produce. Column B is the sounds students actually produced. Column C (created only for your review) specifies whether the response was correct or not. Row 10 Column c is the total of the correct responses for a single student.

A response is considered correct if the cell in column B is either blank or if the letters match with the letters in column A, with or without the slashes and the diacritic marks above the letters. So for example, both B2 and B3 are considered to be correct responses even though B3 didn't include the slashes nor the diacritic mark whereas B2 did. B4 and B6 are also correct because one is blank and the other has the slashes and the diacritic marks stripped away. The only incorrect response here is B5.

I need to create a formula to insert in C10 that generates the total of correct responses, which is 5. Would someone please kindly guide me in the right direction?

In the past, someone from this forum taught me to use the substitute function along with sumproduct, and it worked wonderfully when excel only has to ignore the forward slashes. I tried to apply it with the diacritic marks but I am not successful. There are sounds with various diacritic marks in the rest of this workbook and I would much prefer if you could teach me a generic function so that excel learns to ignore all the special characters and gives me a count of correct response if the letters in column A and column B match.

Please let me know if you have any questions about my request. And thank you so much for your time!

Anita

[AliGW]

Each accented letter is a different character, so you'd need substitution for each character. There may be a way to do it using CHAR numbers, but I think we need a slightly larger set of sample data from you with a few more possible scenarios.

[anita2017]

Dear Ali,
Thank you so much for your prompt assistance. Based on your request, I've scoured my workbooks and had to zoom in on these marks to make sure as they look so small to me. The great news is, the workbook I had shared earlier has all the diatricis that I need to address, which are only 2, such as in the case of /ŭ/ and /ū/. There may be more in the future, but for now, these are all that I have to deal with.

It sounds like I have to specify a substitution for each character. I will look CHAR up, but in the meantime, I'd appreciate any of your suggestions.

Thank you again,

Anita

[JohnTopley]

Unfortunately, the two you mention are not in the standard Excel ascii character set.

[anita2017]

Hi John,

I see. So it sounds like it can't be done in Excel because they are not in the standard Excel character set?

Thank you for letting me know. I will stop googling now as I had spent quite some time looking this up and was unsuccessful.

Thanks again,

Anita

[JohnTopley]

If you have Excel 2013 or above, I believe there are UNICODE/UNICHAR functions which might enable you to do your validation.

[anita2017]

John,

thank you for your advice. i will look into this shortly!

Anita