If you have names given like –
Smith Johnson
Liz lotte
Christy tiara Lewisk
John
And you need to produce abbreviations or acronyms for them like below in all capitals
Smith Johnson – SJ
Liz lotte – LT
Christy tiara Lewisk – CTL
john – J
Then you can use following formula for the same for upto 3 words in the name –
=UPPER(TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","*",2))+1,1)))
Explanation for the formula
1. LEFT(A1,1) – Extracts the first letter from the first name
2. MID(A1,FIND(" ",A1&" ")+1,1) –
FIND(" ",A1&" ") – Find finds the first space in the given name to locate the start of the middle name. " " has been concatenated at the end of A1 so that if there is only first name, FIND will not give error as it will always find the blanks. +1 has been added to start the MID position from where the middle name starts.
3. MID(A1,FIND("*",SUBSTITUTE(A1&" "," ","*",2))+1,1))
SUBSTITUTE(A1&" "," ","*",2) will replace the second blank with a *, hence we can find the position of * to locate the start of last name. As in 2 above, a double space " " has been added in A1 so that FIND always finds the second space. +1 has been added to start the MID position from where the last name starts.
4. TRIM will remove all blanks inserted because of 2 or 3.
5. UPPER will convert the string to all capitals.
Note – If you don't to use the concatenation of single space and double space as in 2 and 3, then IFERROR block can be used. In this case, the formula would become –
=UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")))
Note – This technique can be used to extend up to many words. Only change will be in last block where you can replace 2 with 3, 4,5 and so on in IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"") for 4th, 5th, 6th words and concatenate them….Hence for upto 6 words, the formula would become
=UPPER(TRIM(LEFT(A1,1)&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",3))+1,1),"")&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",4))+1,1),"")
&IFERROR(MID(A1,FIND("*",SUBSTITUTE(A1," ","*",5))+1,1),"")))