Splitting Name/Data in a cell

Sometimes we have to split data in a different cell. e.g. We have Names in column A. And we have to split names as First Name, Middle Name &  Surname. There are two ways to do this activity.

1.  Select the column in which Names are available,  Click on “Data” menu, then click on “Text to column” . Select “File Type” as Delimited. Click “Next” , Select delimiter as “Other” and In a text box Next to other , enter space.  Click Next and then Finish. Data will be distributed in 3 columns.

2. Using Formula -

If Name is in A1 then formula to get

First Name

If Name in column A is “Ajay Kumar Pai” Then Above formula will show first Name only i.e. “Ajay”.  LEFT function returns leftmost characters of a cell, and FIND function return number where space is given in Name. so in above case. FIND function finds the space starting from first character. As first space is after Ajay then it returns number as 5, Then LEFT function returns first 5 leftmost characters i.e. “Ajay”

Last Name 

Above formula first substitutes space after first name as # then finds location of space after middle name as first space is replaced by #. After that it counts the lenght of characters first name and middle name using function LEN and finally it counts total characters and deducts it from length of characters till first space i.e. after middle name. It gives the number of characters in last name and Right function splits rightmost characters upto number of characters of last name and gives last name.

if Middle Name is not in the name i.e.  only first and last name is there like “Ajay Pai”. then another formula is used. i.e.

Middle Name -

It works same as above explanations.

~ Sangram