Switching names and adding period

[natthefatcat]

Hi I am trying to create a formula and I have figured out how to do each seperately but not combined. I would like to be able to reverse order of names and add a "." to any middle initial given. An example would be

Johnson V Edward

I would like it to translate to

Edward V. Johnson in a separate cell.

Any help is appreciated!

 

[JvdV]

Names are notoriously hard to work with. A few questions:

* What would the input be if a person has multiple middle names, e.g.: 'Johnson VE Edward', or rather; How is input displayed when a person has multiple middle names?;
* What would the output be if a person has no middle names but multiple spaces, e.g.: 'Johnson Jackson Edward';
* What version of Excel are you using? You can edit this in your profile settings and will help other help you correctly;

.... probably more questions comming depending on input =)

 

[natthefatcat]

It is unlikely that we would encounter that but I would believe it would show as

Johnson V Adam Edward

It should read as

Edward Adam V. Johnson

 

[JvdV]

So not only would you like to switch first/last name around, you'd also like re-order the words used for both first- & lastname?

I edited my previous comment to include a few other questions for clarification.

 

[natthefatcat]

Sorry I am trying to teach myself excel and this forum I appreciate your patience and help!

Multiple middle names are shown as Johnson Edward V Adam, I need it to be shown as Edward V. Adam Johnson. Or if the middle name isn't shown in full it would show as Johnson Edward V A and I need it to be shown as Edward V. A. Johnson.

If there is no middle name, I just need the names to be flipped to show as Edward Johnson rather than Johnson Edward.

I have Microsoft 365 if that helps.

Very sorry I am not much help, but I really appreciate your help.

 

[natthefatcat]

=MID(D4&" "&D4,SEARCH(" ",D4)+1,LEN(D4))

This is a code I am currently using to flip the names.

This helps it read properly, placing the first name first and then the last name. I am struggling with names that have a middle initial on adding a period after the initial. The formula places the initial in the correct spot so the name reads as Edward V Johnson but I cannot figure out how to place the "." without getting a #value issue for that cell.

 

[JvdV]

That's a bit different to your 1st initial post. Maybe it's best to use the xl2bb tool to showcase different types of input and the desired output. But based on your latest comment I came up with:

Book1
	A	B
1	Johnson Edward V Adam	Edward V. Adam Johnson
2	Johnson Edward V A	Edward V. A. Johnson
3	Edward Johnson	Johnson Edward
Sheet12

Cell Formulas
Range	Formula
B1:B3	B1	=MAP(A1:A3,LAMBDA(z,LET(x,TEXTSPLIT(z,," "),y,IF(LEN(x)=1,x&".",x),TEXTJOIN(" ",,DROP(y,1),TAKE(x,1)))))
Dynamic array formulas.

Note this is different to your initial post and won't work for 'Johnson V Edward' since the above is simply not in the same sequence of First-middle-lastname.