Extract First Name And First Character Of Last Name In Excel

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.

table showing first name and 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.

formula to extract first name and initial of last name in excel

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.




table showing first name along with first character 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.

first name and first character of last name

Hope this helped.




Share The Knowledge

Random Posts

  • Unit Conversion In Excel

    Let’s see how to do unit conversion in excel. We will see how we could leverage a simple convert function […]

    Share The Knowledge
  • Replace Tilde In Excel

    We will see how to find and replace “~” in excel. As tilde is a special character in excel, it […]

    Share The Knowledge
  • Automatically Resize Comment Boxes In Excel

    In this post we will see how we could resize multiple comment boxes to fit the contents in excel at […]

    Share The Knowledge
  • Highlight/Color Negative Value In Excel

    This post demonstrates how to highlight/color negative values in excel. By highlighting negative values in color we enable those values […]

    Share The Knowledge

Leave a Reply