Trick of split first, middle and last name in excel
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 : )