If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it.

In the table below column A has the name in a few different formats. The formula is in column B and  column C displays the formula from column B. Columns C uses the FORMULATEXT function.

The formula in  cell B2 is

=LEFT(A2)&MID(A2,IFERROR(SEARCH(" ",A2),SEARCH(",",A2))+1,1)

The LEFT function, when it doesn’t have the second argument, extracts the first character from a string. First initial done.

The & symbol joins text together.

The MID function extracts text from the middle of a text string.

The second argument in the MID function specifies the starting character position to extract. In our case we have used two SEARCH functions within an IFERROR function for the second argument.

The problem with the SEARCH function is that it returns an error if it can’t find the character string it is looking for.

So I have used the IFERROR function to handle the case where there is no space in the name. The second SEARCH in the IFERROR looks for a comma. So we first look for the space, if there is no space we then look for the comma. If neither exists it would return an error.

The result of the successful SEARCH has 1 added to it to select the character after the space or comma.

The ,1 at the very end is the third argument of the MID function and it specifies how many characters to extract.

Flash Fill

Excel 2013 and later versions have an easy, manual way to do this type of extraction. It’s called Flash Fill and I have written about it here.

In this case we can extract the initials without using formulas. The downside is that if the list expands you need to re-do the shortcut. This is a manual process.

I have changed the names slightly to make sure Flash Fill is working correctly. An example below handles middle initials as well.

In the image below enter the initials you want from the first name. Then select the range to be populated and press Ctrl + E. Job done.

Flash Fill can also handle middle initials.