Extract Initials from Full Name, when Last Name is first

[CRusso1216]

Good afternoon,
I need to extract initials from a full name in my spreadsheet; however, the names are listed in one column as Smith, John. Through Googling, I found the formula =LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),""). This formula works, but produces the results SJ. Can someone help me figure out how to modify this formula so the results produced are JS? Thank you for your help!

 

[Rick Rothstein]

Good afternoon,
I need to extract initials from a full name in my spreadsheet; however, the names are listed in one column as Smith, John. Through Googling, I found the formula =LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),""). This formula works, but produces the results SJ. Can someone help me figure out how to modify this formula so the results produced are JS? Thank you for your help!

Give this a try...

=MID(A1,FIND(" ",A1&" ")+1,1)&LEFT(A1)

 

[dreid1011]

=LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

=IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&LEFT(A2)&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

Change the highlighted portion to match the 2nd formula.

 

[CRusso1216]

Thank you Rick! That worked!

 

[JFry]

Hi there - sorry to jump on an old post here but I used your excellent formula but I can't wrap my head around changes I need to make in order to...
My lists come as SURNAME,Firstname Middlename1 Middlename2.
What I need end-result of is: SURNAME, A.B.C.

Unfortunately, I believe the COMMA throws calculations off if I'm correct? I've manually gone in the list (a drag but if that's how it's gotta be, that's how it's gotta be) and added a space after Surname comma before first name.

The formula used by Dreid1011 :

=IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&LEFT(A2)&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

Takes Andrews, Bob Charlie and
Results in BAC
I'd like it to be: Andrews, B.C. if possible

Is this do-able? I used to be more practiced in excel formulas and referencing cells for custom text combinations, leaving spaces with quotes, and adding punctuation but it's been a number of years!
**IDEALLY** to save work for our admin person, we would leave Surname,First Middle with no space after comma. We get stuff through HR coming in like this and CAN add a space after comma but if there's a way to leave it that would be easiest / more efficient.


Any help greatly appreciated thank you for tips/refreshers.

 

[dreid1011]

You can try this. It's not fancy and can probably be simplified:

=SUBSTITUTE(LEFT(A2,FIND(",",A2&" "))&" "&MID(A2,FIND(",",A2&" ")+1,1)&"."&MID(A2,FIND(" ",A2&" ")+1,1)&"."&MID(A2,FIND(" ",A2&" ",FIND(" ",A2&" ")+1)+1,1)&".","..",".")

This will handle Surname,FirstName MiddleName1 and Surname,FirstName MiddleName1 MiddleName2

Any other configurations would need modifications.

 

[Peter_SSs]

@JFry

Duplicate to: Distilling Surname,Firstname Middlename into Surname, initials

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.

@dreid1011
I have copied your response to the OP's new thread