Match Underlined cell values and return right answer and other language issues

Question

I'm in way over my head and hoping someone can help. I've searched and can't find all the different pieces to put together on this one...

Pretty sure VBA is needed in the solution.

The situation is this: I've been handed a 10k long multiple choice spreadsheet that's been written in Japanese and English. It has a column for the question, the answer written out and then a separate column for each of the 5 multiple choice options.

Question | writAnswer| MCoice1| MChoice2 | MChoice3 | MChoice4 | MChoice5

What I'm expected to produce from this list is another column that gives the number of what multiple choice selection is the right one. Sounds easy, right? That's what I thought till I got started and didn't get too far before the complexity skyrocketed on me.

Issue 1: Multiple languages.
At least that's what I think is going on here. When I run a SEARCH function against the Japanese text:

"私は= I" (the "I" is underlined in my original document) 

using the 'I' in one of the Mchoice cells it is returning an error. It looks like they wrote the original questions using Japanese language fonts and then wrote the answers in English fonts.

I've played with the formula every way you can imagine so I'm fairly certain it's not an id10t error..but you never know..

Issue 2: Multiple correct answers with a 'dumb' search.

All of the wAnswers have the whole answer written out, the pic has an example of this:

 I am a teacher.  (The 'a' is underlined in the original document)

The underline means the student is to select the letter 'a' from the MChoice options. However, the other MChoice fields include "I", "am", and "a" that would match if a straight search if the string was in the wAnswer field or not.

So, this posAnswer column would return the number for which MChoice answer was correct. If the answer in Mchoice1 is correct, '1' is desired, if Mchoice 2 is correct, 2 is returned, and so on for the 5 columns.

Issue 3: Error Catching.

Last but not least it would be great to have some logic in the function that says 'Check Me!' or something along those lines if there are no (0) right answers or more than one right answers to a question.

All of these questions are supposed to have 1 correct answer.

That's as best a description as I can muster up. I'm sure there will be follow up questions but I do thank you for reading this far and trying to help me out!

Update/Edit in response to solution provided by @xidgel below: @xidgel your solution is nearly silver bullt to what I'm looking for. Saved me a massive about of time already. If I can ask for one tweak it could still trim a lot of time off this task, here's a pic of the issue:

The "Answer" field has the answer underlined but the teacher who created this also underlined the space next to the word. Because of this the function is returning 'FALSE' and I have to manually fix the Answer field. There's hundreds, if not thousands of instances of this on the sheet where the space before or after is also underlined. Can the function be adjusted to account for this?

Answer 1

Here's code for user-defined functions that might help.

Public Function IsUnderlineMatch( _
    ByRef LookFor As Excel.Range, _
    ByRef LookIn As Excel.Range) As Boolean

    ' Loops through the underlined text in LookIn
    ' then tests to see if it matches LookFor.
    ' Returns True if a match is found.

    Dim StartAt As Long
    Dim ULText As String
    Dim ULStart As Long
    Dim ULEnd As Long

    IsUnderlineMatch = False
    StartAt = 1
    Do While StartAt <= LookIn.Characters.Count And _
             GetUnderlinedPart(LookIn, StartAt, ULText, ULStart, ULEnd)
        If StrComp(Trim(ULText), Trim(LookFor.Characters.Text), vbTextCompare) = 0 Then
            IsUnderlineMatch = True
            Exit Do
        Else
            StartAt = ULEnd + 1
        End If
    Loop
End Function

Public Function GetUnderlinedPart( _
    ByRef r As Excel.Range, _
    ByVal StartAt As Long, _
    ByRef UnderlinedStr As String, _
    ByRef UnderlineStart As Long, _
    ByRef UnderlineEnd As Long) As Boolean

    ' Searches r for the first group of
    ' consecutive characters that are underlined.
    ' Search starts at StartAt
    ' Returns True if underlined chars were found,
    ' otherwise returns False
    ' On return:
    '   UnderlinedStr holds the chars that were underlined.
    '   UnderlineStart and UnderlineEnd hold the indices
    '   of the start and end of the underlined portion.
    ' If no underlining is found, on return: empty string
    '   UnderlinedStr holds an empty string.
    '   UnderlineStart and UnderlineEnd are 0

    Dim I As Long

    ' Find first underlined char
    I = StartAt
    Do While I <= r.Characters.Count And _
             r.Characters(I, 1).Font.Underline = xlUnderlineStyleNone
        I = I + 1
    Loop

    ' Handle no underline found
    If I > r.Characters.Count Then
        UnderlineStart = 0
        UnderlineEnd = 0
        UnderlinedStr = ""
        GetUnderlinedPart = False
        Exit Function
    End If

    UnderlineStart = I
    ' Find end of contiguous underlined chars
    I = UnderlineStart
    Do While I <= r.Characters.Count And _
             r.Characters(I, 1).Font.Underline <> xlUnderlineStyleNone
        I = I + 1
    Loop

    UnderlineEnd = I - 1
    UnderlinedStr = _
        r.Characters(UnderlineStart, UnderlineEnd - UnderlineStart + 1).Text
    GetUnderlinedPart = True
End Function

To use this in your Excel worksheet do something like:

=IsUnderlineMatch(MChoice,Answer)

The function will return True is underlined text in Answer is an exact match for MChoice; otherwise it will return False. You can test multiple MChoice's against Answer.

Notes:

(A) This code tests for underlining by looking at characters one at a time. I've done this before (in Word IIRC) and it was pretty slow. I don't know how long it will take to get through 10000 rows.

(B) I believe this will work if the underlined text is English/Ascii. It may work if the underlined text is Japanese, but I'm not experienced with handling Unicode and don't know where the pitfalls lie. You may need to tweak this to make it work with non-Ascii.

Hope that helps