Trick of split first, middle and last name in excel

Published by Shoki Singh on

How To Split Full Name To First, Middle And Last Name In Excel with the help of single formula ?

You can use the mid function, substitute function,rept function and trim function to spit a full name to the first, middle and last name with following steps:

step 1. suppose you have a name Rahul kumar , the first thing is to do  put hundred spaces between rahul kumar with help of substitute function and rept function . see below screenshot

here text is your name which is given in A1 cell, So i take reference of A1 cells and old text is your space and for new text we will use rept function to put  100 spaces between name

 

Step 2.  Hit the Enter key and its look like this .

 

now with help of mid function we can get both the name rahul and kumar separately, mid function syntex is look like that

here text is SUBSTITUTE($A$1,” “,REPT(” “,100))

and for start number we will use column function and create dynamic series like that ( 1, 101, 201)

because first name will be starting from character place 1. and kumar will be starting from character place 101 . Same if we have name like this Rahul Kumar Sharma. then sharma start number must be 201. because we put 100 spaces between this name instead of single spaces and series is look like this in form of formula COLUMN(A$1)*100+1-100

and the last perameter of mid function is number of character. We will simply use 100 character because the first , middle and last name is lies between hundred character.

 

but when we take number of character is 100 , some extra spaces is also included this.

 

Step 3. So for remove these extra spaces. We will use trim function

 

now drag this formula and split first, middle and last name only with help of single formula.

Hurrayyyy  :  )

 you can download this file from below link.

 split-firstmiddle-and-last-name-with-single-formula.xlsx 


2 Comments

Bhupender · June 4, 2018 at 10:39 am

Great bro…

Prashant · June 4, 2018 at 2:38 pm

Nice shortcut

Leave a Reply

Your email address will not be published. Required fields are marked *