Make all listed names display in only one way - initials and last name

[Mel Smith]

In a list of over 1,000 names there is a mix of initials and surname or full names. What formula will enable me to convert all names, however displayed, into just forename initial(s) and last name?

For example, the jumbled list contains: J Bloggs, Joseph Bloggs, Joseph C Bloggs, JC Bloggs, Joseph Charles Bloggs, JCD Bloggs and Joseph Charles David Bloggs

Thanks for your help!

Mel

 

[Peter_SSs]

So, you have shown us what the jumbled list might contain but, for those entries, what exact results would you want?

 

[Mel Smith]

Hello Peter,

In my list of examples, I am trying to achieve the following:

J Bloggs - J Bloggs, Joseph Bloggs - J Bloggs, Joseph C Bloggs - JC Bloggs, JC Bloggs - JC Bloggs, Joseph Charles Bloggs - JC Bloggs, JCD Bloggs - JCD Bloggs and Joseph Charles David Bloggs - JCD Bloggs

In essence, all I would like to have displayed all initials, however many and their last name.

Mel

 

[Peter_SSs]

Thanks for the additional information. Does this do what you want?

23 12 14.xlsm
	A	B
1	J Bloggs	J Bloggs
2	Joseph Bloggs	J Bloggs
3	Joseph C Bloggs	JC Bloggs
4	JC Bloggs	JC Bloggs
5	Joseph Charles Bloggs	JC Bloggs
6	JCD Bloggs	JCD Bloggs
7	Joseph Charles David Bloggs	JCD Bloggs
Initials and Name

Cell Formulas
Range	Formula
B1:B7	B1	=LET(P,TEXTBEFORE(A1," ",-1),c,MID(P,SEQUENCE(LEN(P)),1),SUBSTITUTE(CONCAT(IF(EXACT(c,UPPER(c)),c,""))," ",""))&" "&TEXTAFTER(A1," ",-1)

 

[Mel Smith]

Peter, that is brilliant! Thank you very much!

Mel

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.