How to Extract First Letter from Each Word in a Cell in Excel

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”.

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:

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

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 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

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

=ExtractFirstLetter(A1)