CODEWITHSUNDEEP
excelexcel-formulastring-comparisontext-comparisonvba
vnkrishna
vnkrishna

Reputation: 39

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

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!

Upvotes: 3

Views: 1142

Answers (2)

user1274820
user1274820

Reputation: 8144

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).

VBA Window

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.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152585

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.

![enter image description here

Upvotes: 2

Related Questions