How to Extract Initials From a Name in Excel

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.

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.

extract initials from a name1


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.

 Get the position of the nth using excel vba1

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

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

convert column number to letter3

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) & ""
GetFirstCharacters = myResult
End Function

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


extract initials from a name2

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)…

You might also like:


So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *