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

extract first letter from word1

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

#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

extract first letter from word2

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

=ExtractFirstLetter(A1)

extract first letter from word3

 

Sidebar