Distilling Surname,Firstname Middlename into Surname, initials

[JFry]

Hi there - 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.

I found a 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 (bonus points for ALLCAPS surname but not the end of the world)

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.
-Jeff

 

[alansidman]

With your table in Column A, type how you want A1 to look like in B1. Then click on Data Flash Fill and the results spill as shown

 

[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.

 

[dreid1011]

Edit: in response to alansidman

Just a note on this method, or maybe it's just me, but if the very first name only has one middle name, then it skips the second middle name for any with two after that. Or, if the first result has two middle names, it duplicates the first name initial instead of grabbing the middle name initial. Again, maybe I missed something but I thought I might say something too.

 

[Peter_SSs]

SURNAME,Firstname Middlename1 Middlename2.

Takes Andrews, Bob Charlie and

These two have different format (one has a space after the comma, one does not) so I'm not quite sure of your exact format. Anyway, here are two more to try depending on that space. They should handle any number of middle names.

22 12 02.xlsm
	A	B
1	SURNAME,Firstname Middlename1 Middlename2	SURNAME, F.M.M.
2	Jones,Tom Alan Simon Phillip John	JONES, T.A.S.P.J.
3	Smith,Jane	SMITH, J.
4
5	SURNAME, Firstname Middlename1 Middlename2	SURNAME, F.M.M.
6	Jones, Tom Alan Simon Phillip John	JONES, T.A.S.P.J.
7	Smith, Jane	SMITH, J.
Names & Initials

Cell Formulas
Range	Formula
B1:B3	B1	=UPPER(LEFT(A1,FIND(",",A1)))&" "&TEXTJOIN(".",1,IF(CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))<47,MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))+1,1),""))&"."
B5:B7	B5	=UPPER(LEFT(A5,FIND(",",A5)))&" "&TEXTJOIN(".",1,IF(MID(A5,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A5))),1)=" ",MID(A5,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A5)))+1,1),""))&"."

 

[alansidman]

sometimes with flash fill, you need to demo two lines of data and not just one.

 

[shinigamilight]

Will only work for upto 3 middle names

=REPLACE(A1,FIND(",",A1),95,"")&", "&MID(A1,FIND(",",A1)+1,1)&"."&IFERROR(MID(MID(A1,FIND(",",A1)+1,95),FIND(" ",MID(A1,FIND(",",A1)+1,95))+1,1)&".","")&IFERROR(MID(MID(A1,FIND(",",A1)+1,95),FIND("@",SUBSTITUTE(MID(A1,FIND(",",A1)+1,95)," ","@",2))+1,1)&".","")

 

[Peter_SSs]

Will only work for upto 3 middle names

So, just wondering: Is there some advantage to choose that longer formula that only allows up to 3 middle names over the shorter one that allows any number of middle names?
Also, it does not address this which the shorter one does

(bonus points for ALLCAPS surname

 

[shinigamilight]

So, just wondering: Is there some advantage to choose that longer formula that only allows up to 3 middle names over the shorter one that allows any number of middle names?
Also, it does not address this which the shorter one does

Everyone doesn't have access to New functions which you used.

 

[Peter_SSs]

Everyone doesn't have access to New functions which you used.

Sure, but surely we are providing suggestions for the person who asked the question, and they do have access to the functions that I used.