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

 

[JFry]

Uhhhh whoa. Mind Blown. Big thanks to responders here and......I don't know how or why, but I read alansidman 's suggestion and didn't even get to Data / Flash Fill and I SIMPLY started typing into B1:

ANDERSON, B.C.

((**this is only in the case of A SPACE following the post-surname comma**))

And then in next row (B2) just typed first couple letters of next person's name and HOLY MOLY, it populated (light grey as a suggested fill) the entire column and I hit Enter and voila! I didn't do anything else!

This is like Excel granting a wish! Here's my column of names that don't match my required format? I'll just start typing in the next column what I wish for. A Million Dollars. Hmm darn that didn't work hahah



Wow! Hahaha all the time spent on formulas and reading and a big thank you to Mr Excel responders so quickly to my question!

(And thanks to Mod Peter for the note about double posting)

HOWEVER- This quick-fill is only successful if: your source data has a space following the comma after Surname.

With NO SPACE following the post-surname comma (Anderson,Bob Charles) it only WORKS SUCCESSFULLY for the same number of names (Bob Charles being 2) but not for Bob or Bob Charles Dean.

I've also now tried the Data / Flash Fill method and thanks for that!

I haven't yet tried the formulas but shall, for reference, but Whoa Geez the magic learned today is going to save so much time!!! Hopefully it's also a trick that some others can benefit from too!



Jeff

 

[JFry]

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),""))&"."

Hi Peter I've had a go with this.
Our data comes in as
Surname,First Middle Third Fourth

The comma with no space.
And I need
SURNAME, F.M.T.F.

Make sense?

When I tried your formula matching that setup, with my data in a fresh sheet A1 down, I get a result of
Surname, .

So thats Surname then a comma then a space then a period. Absolutely nothing for any desired initials.

?

Thanks
Jeff

 

[JFry]

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)&".","")

Thanks very much - I tried this Lonnnnnnng and detailed formula and......... IT WORKS best for my situation! Now, I think I can also convert it to ALL CAPS by using UPPER?


^^^the above formula generates a perfect end product when working with our incoming format of the comma immediately following Surname. I tried to ask up the chain about having "person at the top" inserting a " " in their formula of gathering the info for Surname, first name, middle name but that didn't go so well. So thank you - I'll use this for my purposes and then "do the work" when encountering >3 middle names.

Thanks again to ALL responders; great to know knowledgeable users out there are willing to help the folks out. I'll stop by again! Cheers!

 

[JFry]

Thanks very much - I tried this Lonnnnnnng and detailed formula and......... IT WORKS best for my situation! Now, I think I can also convert it to ALL CAPS by using UPPER?


^^^the above formula generates a perfect end product when working with our incoming format of the comma immediately following Surname. I tried to ask up the chain about having "person at the top" inserting a " " in their formula of gathering the info for Surname, first name, middle name but that didn't go so well. So thank you - I'll use this for my purposes and then "do the work" when encountering >3 middle names.

Thanks again to ALL responders; great to know knowledgeable users out there are willing to help the folks out. I'll stop by again! Cheers!

PS - I added a third column (C) containing only =UPPER(B1) and it easily changes everything in the B column to ALL CAPS.

If I wanted to include UPPER into the long formula, would I enclose it with brackets at the start or end of the formula? Likely doesn't matter?

 

[Peter_SSs]

When I tried your formula matching that setup, with my data in a fresh sheet A1 down, I get a result of
Surname, .

That would indicate that the 'space' characters are not normal space characters, most likely CHAR(160)
If the spaces are CHAR(160) you could give this a try instead for unlimited middle names.

Jfry.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.
Names & Initials (2)

Cell Formulas
Range	Formula
B1:B3	B1	=UPPER(LEFT(A1,FIND(",",A1)))&" "&TEXTJOIN(".",1,IF(ISNUMBER(FIND("|"&CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))&"|","|44|160|")),MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))+1,1),""))&"."

 

[shinigamilight]

PS - I added a third column (C) containing only =UPPER(B1) and it easily changes everything in the B column to ALL CAPS.

If I wanted to include UPPER into the long formula, would I enclose it with brackets at the start or end of the formula? Likely doesn't matter?

Yes, just enclose the whole formula within an upper function it will get the job done.