Let's say you have imported a list of names into Microsoft  Excel  from a payroll program, and the first names and surnames are all added to one column of data but in order to manipulate the data you would like to have the surnames extracted to a separate column. You can do this using various methods of extracting text from a text string. In this tip we show you how, by nesting the MID and FIND functions, the surname can be extracted from the name string.

• The MID function returns a specific number of characters from a text string, starting at the position you specify.
• The FIND function locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 and 2013.
The screen shots below will be used to illustrate this exercise.

 
1.                 Select cell C3.

2.                 Select as per screen shot below

3.                 Select MID from the Text functions list.

4.                 Then enter the formula as below.

B3 refers to the first cell under the names column

• Excel searches for the position of the space within the text string using the FIND function.
• 1 is added to the returned number
  • This is the first   letter of the surnames.
• 10 is the maximum number of characters that can be extracted.

5.                 Select OK and copy the formula down.

6.                 The result will be as below.