To abbreviate names that contain capital letters, we will introduce a custom Excel function. The ABBREVIATE function returns the abbreviation for the supplied string. We’ll show you two different methods.
Abbreviate names using user-defined function
The abbreviate text contains only capital letters. If you want to extract the initials from names or create acronyms, you should use a very complex excel Excel formula.
Or write a small function to speed up your work. Let’s see the first example!
Generic Formula
=Abbreviate(cell_reference)
Special requirement before applying the function: The source text must include capitalized words.
In the example shown, the formula in B2 is:
=Abbreviate(A2)

To implement this user-defined function into your spreadsheet, do the following:
- Just press Alt + F11 to open the VBA Editor
- Right-click on the ThisWorkbook
- Add a new module
- Copy and Insert the snippet
- Save your workbook as to a .xlsm file.
From now, you can use the function.
The source code:
Function Abbreviate(strC As String) As String
' Return the abbreviation for the supplied string.
Dim Company() As String ' Company name array
Dim i As Byte, j As Byte ' Number of words and counter.
Dim strAbbr As String ' String of abbreviation.
Company() = Split(strC, " ")
i = UBound(Company()) ' Total number of elements.
If i > 0 Then ' If more than one element.
For j = 0 To i
' Build abbreviation if each word.
strAbbr = strAbbr & UCase(Left(Company(j), 1))
Next j
Else
strAbbr = strC ' If one word only, return the word.
End If
Abbreviate = strAbbr ' Value returned by the function.
End Function
Alternative way to use user-defined function
- Use DataFx, our free excel add-in
- Download the tool
- Follow the add-in installation steps

Abbreviate names using regular Excel Formula
Let us see how to check when a cell contains specific text use a simple combination of regular excel functions. Oh no. It’s terrible… but if you are a function geek, try to use it.

=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
ROW(INDIRECT("65:90")),0)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
Explanation:
- PROPER capitalize on the word if it’s necessary.
- The INDIRECT function has a parameter: 65 to 90. This range correspond to the ASCII codes for all capital letters between A-Z.
- We are using MID, ROW, INDIRECT, and LEN functions to convert a string to letters.
Good to know: The new TEXTJOIN function is available only in Office365 and Excel 2019.