Initializing a single middle name is easy using a simple 3 steps:
=LEFT(name,FIND(" ",name)) &
IF(COUNTIF(name,"* * *"),MID(name,FIND(" ",name)+1,1)&". ","") &
RIGHT(name,LEN(name)-FIND(" ",name,FIND(" ",name)+COUNTIF(name,"* * *")))
Step 1:
Get the first name => LEFT(name,FIND(” “,name))
Step 2:
Check if the name has a middle part => COUNTIF(name,”* * *”)
If TRUE, get the first letter of the middle name and concatenate with a decimal => MID(name, FIND(” “, name)+1,1)&” . “
Step 3:
Find the position of the last space in the name => FIND(” “,name,FIND(” “,name)+COUNTIF(name,”* * *”))
Find the length of the last name by Getting the length of the full name LEN(name) and deduct the position of the last space
Finally, concatenate all the results of the 3 steps above
When you have more than 1 middle name, the easiest way to do this is by using a LAMBDA or POWER QUERY
Initializing any number of middle names Using a LAMBDA
=LAMBDA(string, LET( split, TEXTSPLIT(string, " "), fname, TAKE(split, ,1), inits, IFERROR(LEFT(DROP(DROP(split, , -1), , 1))&".",""), sname, TAKE(split, , -1), TEXTJOIN(" ",, fname, inits, sname) ))
Initializing any number of middle names Using POWER QUERY
π₯ππ
πππ¦ππ¬ = πππ±π.ππ©π₯π’π([π πππ ππππ]," "),
ππ§π’ππ’ππ₯π¬ = ππ’π¬π.ππ«ππ§π¬ππ¨π«π¦(
πππ¦ππ¬, ππππ‘ πππ±π.ππππ«π(_,1) &"."),
ππ«π¨π©1π¬π= ππ’π¬π.πππ¦π¨π―ππ π’π«π¬ππ(ππ§π’ππ’ππ₯π¬,1),
ππ«π¨π©πππ¬π = πππ±π.ππ¨π¦ππ’π§π(
ππ’π¬π.πππ¦π¨π―ππππ¬ππ(ππ«π¨π©1π¬π,1)," ")
π’π§
ππ’π¬π.π π’π«π¬π(πππ¦ππ¬) &" "& ππ«π¨π©πππ¬π&" "&ππ’π¬π.πππ¬π(πππ¦ππ¬)
For Power Query, you have to use the above M Code as Shown below
Recent Comments