This post will guide you how to extract first letter from each word in a given cell in Excel. How do I extract the first letter of each word in a range of names in your current worksheet in Microsoft Excel 2013/2016. Assume that you have a range of First, Second and third names and you would like to extract the initials to a separate cell.

For example, one name called “Nigolas Kate”, and you want to extract the first letters so that the result would be “NK”.

1. Extract First Letter from Each Word in a Cell Using User Defined Function with VBA

Since there is not built-in function to extract the first letter of each word in Excel, and you can define a user defined function by Excel VBA code to achieve the result. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractFirstLetter(text) As String
    mystring = Left(text, 1)
    For i = 2 To Len(text) - 1
        If Mid(text, i, 1) = " " Then
            mystring = mystring & Mid(text, i + 1, 1)
        End If
    Next i
    ExtractFirstLetter = WorksheetFunction.Substitute(UCase(mystring), " ", "")
End Function

Step5: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=ExtractFirstLetter(A1)

2. Extract First Letter from Each Word in a Cell Using Formula

If you want to extract the first letter from each word in a cell in Excel, you can use a combination of the CONCATENATE, LEFT, MID and FIND functions and return them as a concatenated string.

You need to enter the following formula in a blank cell:

=CONCATENATE(LEFT(A1,1),MID(A1,FIND(" ",A1)+1,1),IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),""))

Note: Replace “A1” with the cell reference of the cell you want to extract the first letters from.

Press Enter to apply the formula.

You can then copy and paste the formula to apply it to other cells, or drag the fill handle to apply it to a range of cells.

Here’s how this formula works:

The LEFT function is used to extract the first letter of the first word in the cell.

The MID and FIND functions are used to extract the first letter of the second and subsequent words in the cell.

The CONCATENATE function is used to join the first letters of each word in the cell.

The IFERROR function is used to handle cells with only one or two words. If the cell has only one or two words, the formula returns the first letter of those words.

3. Video: Extract First Letter from Each Word in a Cell in Excel

If you want to learn how to extract the first letter from each word in a cell in Excel, you can watch this video that shows you how to use a formula or a VBA code to achieve this task.