This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016.

Assuming that you have a list of data in range B1:B4, in which contain name values, and you want to extract initials from these names. How to accomplish it. this post will show you two methods to get initials.

1. Extract Initials from a Name Using a Formula

To extract initials from a given name in Excel, you can do this using a formula based on the LEFT function, the IF function, the ISNUMBER function and the MID function. Like this:

=LEFT(B1)&IF(ISNUMBER(FIND(" ", B1)),MID(B1,FIND(" ", B1)+1,1),"")&IF(ISNUMBER(FIND(" ", B1,FIND(" ", B1)+1)),MID(B1,FIND(" ", B1,FIND(" ", B1)+1)+1,1),"")

You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle down to other cells.

2. Extract Initials from a Name Using User Defined Function

You can also define a User Defined function to extract initials from a given name in Excel. 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 GetFirstCharacters(pWorkRng As Range) As String
myValue = pWorkRng.Value
myStr = VBA.Split(Trim(myValue))
For i = 0 To UBound(myStr)
    myResult = myResult & VBA.Left(myStr(i), 1) & ""
Next
GetFirstCharacters = myResult
End Function

Step5# type the following formula into a blank cell and press Enter key in your keyboard.

=GetFirstCharacters(B1)

3. Video: Extract Initials From a Name

This video will desmonstrate you how to extract initials from a given name using both formula and VBA code in Excel.

4. Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel ISNUMBER function
  The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…