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

    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


Print Friendly, PDF & Email

Do you want to excel in Excel?


Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.