Forum Discussion
Text Filter
Hi,
Please try this formula to compare two names regardless of the middle initial:
=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))=TRIM(LEFT(B1,FIND(" ",LOWER(B1),1))) & " " & TRIM(MID(B1,FIND(" ",LOWER(B1),FIND(" ",LOWER(B1),1)+1)+1,LEN(B1)-FIND(" ",LOWER(B1),1)+1))
The formula is found in this https://www.extendoffice.com/documents/excel/1779-excel-remove-middle-initial.html, I've applied it on two cells and compare them in terms of equality using = sign.
Regards
Is there a way to tweak the formula to list names as last, first, middle initial? The formula works except it still shows false for names listed not exactly the same... for example Jones Santa C is false to Jones Santa...
It seems difficult to add this odd case to the formula, so I suggest to separate the names that have the initial in the last in a separate sheet and compare them using the below formula.
Or alternatively, you can add it in the next column next to the previous formula as a second check.
=IF(LEN(TRIM(A1))-LEN(TRIM(SUBSTITUTE(A1," ","")))+1=3,TRIM(SUBSTITUTE(A1,MID(A1,LEN(A1)-1,2),"")),A1)=IF(LEN(TRIM(B1))-LEN(TRIM(SUBSTITUTE(B1," ","")))+1=3,TRIM(SUBSTITUTE(B1,MID(B1,LEN(B1)-1,2),"")),B1)
Hope that helps
