Naming Format Formula

[Bluez13]

Hi All,

I am trying to perfect a formula so that I can take the first name middle initial (when present) and last name and format a certain way.

John T. Doe or John Doe

So far I have this formula that works when there is a middle initial present, but will leave the "Space" and "." in when there is no middle initial.

=A2&" "&IF(ISBLANK(K2),"",K2&". ")&C2

The spreadsheet works so that all I have to do is paste into the first 4 cells and the rest will autofill based from those cells. I then save as a csv to bulk upload for accounts. The "displayname" is where the above formula is at. Order of the cells does not matter past "D"

Also to note that the reference cell K2 has a formula to take the full middle name (from B2) and just format it to 1 letter. =LEFT(B2,1)

Any help with this would be GREATLY appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	John	Tom	Doe				John T.Doe
3	Jane		Doe				Jane.Doe
Main

Cell Formulas
Range	Formula
G2:G3	G2	=TEXTJOIN(" ",,A2,LEFT(B2))&"."&C2

 

[jtakw]

Hi,

Change ISBLANK to ="" (since you have formula in K2)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	John	Thomas	Doe		John T. Doe						T
3	John		Doe		John Doe
Sheet1049

Cell Formulas
Range	Formula
E2:E3	E2	=A2&" "&IF(K2="","",K2&". ")&C2
K2:K3	K2	=LEFT(B2,1)

 

[Fluff]

Think I misunderstood what you wanted.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	John	Tom	Doe				John T. Doe
3	Jane		Doe				Jane Doe
4
Main

Cell Formulas
Range	Formula
G2:G3	G2	=TEXTJOIN({" ",". "},,A2,LEFT(B2),C2)

 

[jtakw]

Also, if you want to get rid of K2, you can use the formula in my sample F2:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	John	Thomas	Doe		John T. Doe	John T. Doe					T
3	John		Doe		John Doe	John Doe
Sheet1049

Cell Formulas
Range	Formula
E2:E3	E2	=A2&" "&IF(K2="","",K2&". ")&C2
F2:F3	F2	=SUBSTITUTE(TRIM(A2&" "&LEFT(B2,1)&" "&C2)," ",". ",2)
K2:K3	K2	=LEFT(B2,1)

 

[Bluez13]

Thank you so much!! The spreadsheet is finally finished!

Hi,

Change ISBLANK to ="" (since you have formula in K2)

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	John	Thomas	Doe		John T. Doe						T
3	John		Doe		John Doe
Sheet1049

Cell Formulas
Range	Formula
E2:E3	E2	=A2&" "&IF(K2="","",K2&". ")&C2
K2:K3	K2	=LEFT(B2,1)

Thank you so much!! It is finally finished. Greatly appreciated!!

 

[jtakw]

You're welcome, thanks for the feedback.