remove middle initial + concatenate + lowercase

[Simply_Me]

Hello! I a list of names (more than a thousand names) and I want to remove the middle initial if any the concatenate the firstname and lastname in lowercase, like the one shown below. I have this initial formula which partially does the work of removing the initial in certain names.

=IF((IFERROR((FIND(" ",A2,((FIND(" ",A2,1))+1)))/(FIND(" ",A2,((FIND(" ",A2,1))+1))),0))=1,LEFT(A2,((FIND(" ",A2,((FIND(" ",A2,1))+1)))-1)),A2)

I know also that I have to use lower() to convert everything to lowercase.

Can anyone please help me on this? Thank you!

[Fluff13]

With that example, how about
=LOWER(B2&SUBSTITUTE(IFERROR(LEFT(A2,FIND(".",A2)-2),A2)," ",""))

[Simply_Me]

With that example, how about
=LOWER(B2&SUBSTITUTE(IFERROR(LEFT(A2,FIND(".",A2)-2),A2)," ",""))

Thanks for the reply. Using the above code this is now the output. Everything is okay except the last name where there is no period for the middle initial.

[Rick Rothstein]

On the off-chance the middle initial, when present, might not have an attached period at the end, give this formula a try...

=LOWER(SUBSTITUTE(B2&IF(OR(RIGHT(A2)=".",MID(A2,LEN(A2)-1,1)=" "),LEFT(A2,LEN(A2)-2),A2)," ",""))

[Simply_Me]

On the off-chance the middle initial, when present, might not have an attached period at the end, give this formula a try...

=LOWER(SUBSTITUTE(B2&IF(OR(RIGHT(A2)=".",MID(A2,LEN(A2)-1,1)=" "),LEFT(A2,LEN(A2)-2),A2)," ",""))

Thank you for your reply. you're right sir that there is a chance that a period might be present so I went ahead and tried your code and it works but then I was also reminded that there are times that a person has two middle initials so I added names for that purpose and below is the result.

[Vraag en antwoord]

With that example

=LOWER(SUBSTITUTE(LEFT(B2&A2,LEN(B2&A2)-2*(RIGHT(A2)="."))," ","")) 

[Simply_Me]

With that example

=LOWER(SUBSTITUTE(LEFT(B2&A2,LEN(B2&A2)-2*(RIGHT(A2)="."))," ","")) 

Thanks for the reply sir. Using your formula, I got the following output, but just like what I posted earlier, what if a person has two middle initials?

[Bo_Ry]

Please try

=LOWER(SUBSTITUTE(B2&LEFT(A2,FIND(".",A2&" .")-2)," ",))

[Phuocam]

How do you want the result?

1. John G. W. Doe -> ?

2. John G W Doe -> ?

3. John G.W Doe -> ?

[Simply_Me]

The result should be "doejohn"

[Phuocam]

Try this:

=LOWER(SUBSTITUTE(B2&IF(AND(MID(TRIM(SUBSTITUTE(A2,"."," ")),LEN(TRIM(SUBSTITUTE(A2,"."," ")))-3,1) = " ",
MID(TRIM(SUBSTITUTE(A2,"."," ")),LEN(TRIM(SUBSTITUTE(A2,"."," ")))-1,1) = " "),
REPLACE(TRIM(SUBSTITUTE(A2,"."," ")),LEN(TRIM(SUBSTITUTE(A2,"."," ")))-3,4,""),
IF(MID(TRIM(SUBSTITUTE(A2,"."," ")),LEN(TRIM(SUBSTITUTE(A2,"."," ")))-1,1) = " ",
REPLACE(TRIM(SUBSTITUTE(A2,"."," ")),LEN(TRIM(SUBSTITUTE(A2,"."," ")))-1,2,""),A2))," ",""))

[Simply_Me]

PERFECT! Thank you very much sir!

[Phuocam]

You're welcome! Thanks for the reputation points.

[Bo_Ry]

Another one

=LOWER(SUBSTITUTE(B2&LEFT(A2,SEARCH(" ? ",TRIM(SUBSTITUTE(A2,"."," "))&" "))," ",))

[Rick Rothstein]

Another one

=LOWER(SUBSTITUTE(B2&LEFT(A2,SEARCH(" ? ",TRIM(SUBSTITUTE(A2,"."," "))&" "))," ",))

That did not seem to work against these names from Message #8... Katrine Andrea, Maria Annie Gale and Emely.

If I am not mistaken, I believe this formula will work...

=LOWER(SUBSTITUTE(B2&IFERROR(LEFT(A2,SEARCH(" ?.*?.",A2)-1),IFERROR(LEFT(A2,SEARCH(" ?.",A2&".")-1),A2))," ",""))

[Simply_Me]

That did not seem to work against these names from Message #8... Katrine Andrea, Maria Annie Gale and Emely.

If I am not mistaken, I believe this formula will work...

=LOWER(SUBSTITUTE(B2&IFERROR(LEFT(A2,SEARCH(" ?.*?.",A2)-1),IFERROR(LEFT(A2,SEARCH(" ?.",A2&".")-1),A2))," ",""))

Thank you for this post. It works fine except in the it wont work against the name "John G W"

[Rick Rothstein]

Thank you for this post. It works fine except in the it wont work against the name "John G W"

Okay, this shorter formula appears to handle all of your situations correctly...

=LOWER(SUBSTITUTE(B2&LEFT(A2,SEARCH(" ? ? ",TRIM(SUBSTITUTE(A2,"."," ")&" X X")&" ")-1)," ",""))