Forum Discussion
Text Filter
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
In the absence of extra spaces, this formula returns your desired result:
=IF(LEN(A2)=LEN(B2),LEFT(A2,LEN(A2)-1)=LEFT(B2,LEN(B2)-1),
IF(LEN(A2)>LEN(B2),ISNUMBER(FIND(B2,A2)),
ISNUMBER(FIND(A2,B2))))See the examples in the attached file and inform me of your thoughts thereon.
