Text Filter

AsiaY 

Sample is in Sheet3 attached, but I'm missed with the logic:

- Are names always as LastName FirstName <MiddleName or Initial> where the latest is optional? Or that could be like LastName <MiddleName> FirstName?

- Names as "Calendar Amy L" and "Calendar Amy Lynn" are considered as the same. But what about "Calendar Amy J" or whatever is on the third position - is that the same "Calendar Amy"?

For the collection, Power Query variant

by

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    RemoveMiddle = Table.AddColumn(Source, "Different Names", each
        let
            splitNames = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Names])
        in
            Text.Combine({splitNames{0}, " ", List.Last(splitNames)}),
    type text),
    RemoveDuplicates = Table.Distinct(RemoveMiddle, {"Different Names"}),
    RemoveSource = Table.SelectColumns(RemoveDuplicates,{"Different Names"})
in
    RemoveSource

Actually no coding is required, script is generated by Column By Example.

AsiaY 

Just to demonstrate that my post above wasn't merely the product of a deranged mind, I attach a functioning workbook (standard installation rather than insider).  The name 'testName' is now a relative reference to a cell in the column header and the sequence 'k' is now somewhat more turgid

k: =COLUMN( INDEX(aRow,1):INDEX(aRow,n))

[not that a number sequence should rely upon the definition of a somewhat arbitrary range]

AsiaY 

Warning: This is an experiment testing formulas using modern Dynamic Arrays.

n: = LEN(@testName); select a name from the column headers and determine its length

k: = SEQUENCE(1,n); define a sequence counter for characters

chr: = MID( @testName, k, 1 ); split the test name into separate characters

firstName: = LEFT( @testName, MIN( IF( chr=" ", k ) ) ); extract first name

lastName: = RIGHT( @testName, n - MAX(IF( chr=" ", k ) ) ); extract last name

Worksheet formula:

= ( LEFT(fullName, LEN(firstName)) = firstName) *

   (RIGHT(fullName, LEN(lastName)) = lastName);

compares an array of full names against the test name.

AsiaY

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