In this pose we will see how to extract first name and initial of last name in excel given the full name.
Suppose we have been given full name in column A which consists of first name and last name separated by space and we are required to extract the first name and only the initial of the last name.
In the pic below, you could see in column A we have names and in column B is the output that we want i.e. just first name and the first character of last name.
We will use a combination of LEFT and FIND function in excel to extract first name and the first character of name as shown above.
LEFT(text, length) function extracts the string from text starting from the first position to the number of characters denoted by length.
FIND(text, character,starting_pos) function finds the position of any particular character that we provide in the second argument, the third argument starting_pos is optional which lets you decide from where the excel should start searching the character.
We will try to build a formula to get the first name along with the initial of last name by applying these formulae.
Enter the formula =LEFT(A2,FIND(” “,A2)+1) in cell B2 as shown in the pic below.
As you could see we are extracting the string till one position further where we encounter a space in the full name.
So FIND(” “,A2) is giving us the position of the space and +1 in the formula is because I want to extract the first character from the last name, if I were to extract first 3 characters from the last name I would give +3 in the formula.
You could see below in the screen shot that we have got the output with first name and an the initial of last name.
So our LEFT function is extracting the first name and the first character of the last name with the help of FIND function in excel.
Now drag the formula till row 6 as shown below.
Hope this helped.
