Convert LastName, FirstName in Excel to FirstName LastNameInitial

[pasujo]

Hello,
I am looking for a function to convert LastName, FirstName to FirstName LastNameInitial in Excel. I have seen several name conversion options, but not this one specifically; however, I am new to this Forum so I apologize if I missed it.

Example:

Thank you!

 

[JamesCanale]

MrExcelPlayground19.xlsx
	B	C
25	Smith, John	John S.
26	Jones, Mary Ellen	Mary Ellen J.
27	Phillips, Phil	Phil P.
Sheet1

Cell Formulas
Range	Formula
C25:C27	C25	=MID(B25,SEARCH(",",B25)+2,99)&" "&LEFT(B25,1)&"."

 

[pasujo]

MrExcelPlayground19.xlsx
	B	C
25	Smith, John	John S.
26	Jones, Mary Ellen	Mary Ellen J.
27	Phillips, Phil	Phil P.
Sheet1

Cell Formulas
Range	Formula
C25:C27	C25	=MID(B25,SEARCH(",",B25)+2,99)&" "&LEFT(B25,1)&"."

That formula is not working for me, here is what I'm getting. (Side note - I'm not sure why my columns show numbers instead of letters.)

 

[JamesCanale]

for laughs, try this.

MrExcelPlayground19.xlsx
	B	C
25	Smith, John	John S.
26	Jones, Mary Ellen	Mary Ellen J.
27	Phillips, Phil	Phil P.
Sheet1

Cell Formulas
Range	Formula
C25:C27	C25	=CONCATENATE(MID(B25,SEARCH(",",B25)+2,99)," ",LEFT(B25,1),".")

Any chance you are running a much older version of excel than you list (2016)

 

[pasujo]

for laughs, try this.

MrExcelPlayground19.xlsx
	B	C
25	Smith, John	John S.
26	Jones, Mary Ellen	Mary Ellen J.
27	Phillips, Phil	Phil P.
Sheet1

Cell Formulas
Range	Formula
C25:C27	C25	=CONCATENATE(MID(B25,SEARCH(",",B25)+2,99)," ",LEFT(B25,1),".")

Any chance you are running a much older version of excel than you list (2016)

Hmmm. I got the same thing...

Here is my product info...

 

[MARK858]

Side note - I'm not sure why my columns show numbers instead of letters.

Try

=MID(RC[-1],SEARCH(",",RC[-1])+2,99)&" "&LEFT(RC[-1],1)&"."

 

[JamesCanale]

This looks like an Office 365 - which is what I am running. I can't say I understand why it's broken.

 

[pasujo]

Try

=MID(RC[-1],SEARCH(",",RC[-1])+2,99)&" "&LEFT(RC[-1],1)&"."

YAY! This one worked for me. Thank you, thank you!

 

[pasujo]

This looks like an Office 365 - which is what I am running. I can't say I understand why it's broken.

Yea, I'm not sure why it's not working either. The formula posted from MARK858 worked for me. Appreciate your help!

 

[MARK858]

@JamesCanale his Excel is in R1C1 format

@pasujo another option to reset your Excel to column letters

1. On the File menu, click Options.
2. Click the Formulas tab.
3. Under Working with formulas, click to clear the R1C1 reference style check box and then click OK.