Last Name, First Name, MI

[Wulf]

There are several SIMILAR threads, but I couldn't find one that fit this:

I need to turn "Last Name, First Name MI" in cell I9 on Sheet1 into:

"Last Name" in E9, "First Name" in R9, and "MI" in AF9 on Sheet1.5, with it also showing "" (blank) in AF9 if there is no "MI" in I9 on Sheet1.

I've been able to extract the last name, and even the first name....but if that's all there is, then it puts the last letter of the first name as the middle initial, and if there is a middle initial, that cell goes blank.

????

Thanks!

 

[bryonwoods30]

use this formula to test for how many spaces there are, replace A1 with your cell containing the name. then for your middle initial, use an if statement based on the result of this, if you get 2 spaces there is a middle initial, if only 1 space then middle initial is blank.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

 

[Scott Huish]

You can't base it on that, what if the first or last name has more than one word?

 

[Scott Huish]

What is the format of your Middle Initial, is it just a single letter a single letter with a period or both?

 

[Wulf]

What is the format of your Middle Initial, is it just a single letter a single letter with a period or both?

The format should always end up being a standard:

Doe, John Q

 

[Scott Huish]

Try this:

Formula in E9:
=LEFT(I9,FIND(",",I9&",")-1)

Formula in AF9:
=IF(LEN(TRIM(RIGHT(I9,2)))=1,RIGHT(I9),"")

Formula in R9:
=TRIM(REPLACE(LEFT(I9,LEN(I9)-((AF9<>"")*2)),1,FIND(",",I9),""))

 

[Wulf]

I ended up with the result that I was looking for with a series of formulae:


AQ9=IF('1'!I9="","",SUBSTITUTE('1'!I9,".",""))
AQ10=SUBSTITUTE(AQ9," ","",1)
AQ11=SUBSTITUTE(AQ10," ",".")
AQ12=SUBSTITUTE(AQ11,",",".")
AQ18=SUM(LEN(AQ9)-LEN(SUBSTITUTE(AQ9," ","")))

AW9=IF(AQ9="","",LEFT(AQ9,FIND(",",AQ9,1)))
AW10=IF(AQ10="","",LEFT(AQ10,FIND(",",AQ10,1)))
AW11==IF(AQ18=2,IF(AQ10="","",RIGHT(AQ10,FIND(",",AQ10,1)+2)),IF(AQ10="","",RIGHT(AQ10,FIND(",",AQ10,1))))
AW12=RIGHT(AQ12,1)

BB11=IF(AQ18=2,IF(AW11="","",LEFT(AW11,FIND(" ",AW11,1))),AW11)

BH10=SUBSTITUTE(AW10,",","")
BH11=IF(AQ18=1,BB11,BB11)
BH12=AW12

Which, all ends up with the result of:
E9=BH10 Doe
R9=BH11 John
AF9=IF(AQ18=1,"",BH12) Q

All coming from I9, regardless if it is entered in as "Doe, John Q.", or "Doe, John Q", and gives me a blank for AF9 if entered as "Doe, John".

 

[Wulf]

Try this:

Formula in E9:
=LEFT(I9,FIND(",",I9&",")-1)

Formula in AF9:
=IF(LEN(TRIM(RIGHT(I9,2)))=1,RIGHT(I9),"")

Formula in R9:
=TRIM(REPLACE(LEFT(I9,LEN(I9)-((AF9<>"")*2)),1,FIND(",",I9),""))

And, of course, all of yours works, lol. The only potetnital problem I found is if someone actually ends up with the "." behind the middle initial.

 

[Scott Huish]

That's why I asked.

 

[Scott Huish]

Change the AF9 formula to:

=IF(LEN(TRIM(RIGHT(I9,2)))=1,RIGHT(I9),IF(AND(RIGHT(I9)=".",LEN(TRIM(RIGHT(I9,3)))=2),LEFT(RIGHT(I9,2)),""))