Ignoring Middle name/ initial in match

[Areallusernamestaken]

Hi,

How can I make this formula ignore middle initials/ names that appear on either the Sheet I’m working on (Active Sheet) or on the sheet I’m pulling from (the Master Sheet)?

This is what I currently have in cell A2 (this version can't handle middle names/ initals):
=TRIM(IFERROR(INDEX(Master Sheet Column A,MATCH(RIGHT(B2,LEN(B2)-FIND(" ",B2))&", "&LEFT(B2,FIND(" ",B2)-1), Master Sheet Column B,0),1),"Fill in"))

Active Sheet:

Master Sheet:

Thanks in advance!!!

 

[johnnyL]

Two formulas that should work, you only need one or the other, but both should accomplish what you want.

=TRIM(LEFT(B2,FIND(" ",LOWER(B2),1))) & " " & TRIM(MID(B2,FIND(" ",LOWER(B2),FIND(" ",LOWER(B2),1)+1)+1,LEN(B2)-FIND(" ",LOWER(B2),1)+1))

OR

=LEFT(B2,FIND(" ",B2))&TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",99)),99))

 

[johnnyL]

Here is a line of VBA code to strip the middle names/initials from a column:

    Columns("B:B").Replace What:=" * ", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

[Peter_SSs]

For the name formats that you have shown in those samples, see if this helps.

Areallusernamestaken.xlsm
	A	B
1	ID	Name
2	1111	Apples, Adam R
3	2222	Barnes, Bert
4	3333	Clements, Cathy Stacy
5	4444	Delacruz, Dorothy
Master sheet

Areallusernamestaken.xlsm
	A	B
1	ID	Name
2	1111	Adam Apples
3	2222	Bert P Barnes
4	3333	Cathy Y Clements
5	4444	Dorothy Delacruz
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=XLOOKUP(TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",50)),50))&" "&LEFT(B2,FIND(" ",B2)-1),TRIM(LEFT(SUBSTITUTE(SUBSTITUTE('Master sheet'!B$2:B$10,",","")," ",REPT(" ",50)),100)),'Master sheet'!A$2:A$10,NA())

BTW, the answer to your username is "no"

 

[Areallusernamestaken]

Thank you johnnyL and thank you Peter_SSs! I appreciate your help!

Peter_SSs, yeah... I got a little frustrated when I was trying to register because any username I typed in was "taken." So I finally did this one and it worked!

 

[Peter_SSs]

You're welcome. Glad we could help.