String comparison between two cells containing text but one containing multiple delimiters

Question

I wish to compare two cells in excel which contain similar text but one containing different delimiters in it. I want to ignore the delimiters while comparing the strings.

Eg.

John Doe: Mary Ann. Are Married/
John Doe Mary Ann Are Married

I am totally unaware of macros. Any leads are appreciated!

Answer 1

You can try this:

Function CompareByLetter(t1 As String, t2 As String) As Boolean
CompareByLetter = CleanString(t1) = CleanString(t2)
End Function
Function CleanString(t As String) As String
Dim t1, x, c
For x = 1 To Len(t)
    c = Asc(UCase(Mid(t, x, 1)))
    If (c >= 65 And c <= 90) Or c = 32 Then t1 = t1 & Mid(t, x, 1)
Next x
CleanString = t1
End Function

Then you can use it as a formula:

=CompareByLetter(A1,A2)

This macro just compares strings by only keeping letters and spaces.

You can also use =CleanString(A1) to remove all other characters from your strings.

To use this in your project, open excel and press ALT+F11

Right click off to the left side and select Insert -> Module

Paste the code into the module window on the right (see attached).

Image mirror since Stackoverflow image hosting seems down

After that, you should be able to use the functions as you would a formula.

Just enter =CompareByLetter(A1,A2) in a cell.

Answer 2

If you have Office 365 Excel then we can use this array formula:

=TEXTJOIN("",TRUE,IF(((CODE(UPPER(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)))>=65)*(CODE(UPPER(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)))<=90))+(CODE(UPPER(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)))=32),MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1),""))=TEXTJOIN("",TRUE,IF(((CODE(UPPER(MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1)))>=65)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1)))<=90))+(CODE(UPPER(MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1)))=32),MID(A2,ROW(INDIRECT("1:" & LEN(A2))),1),""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.