Learn how to abbreviate names or words using regular Excel functions or the ABBREVIATE user-defined function.
First, we will introduce the ABBREVIATE function; we will use a UDF to simplify and speed up the task. Then, in the second part of the article, we’ll apply a custom formula to abbreviate names containing capital letters.
Today’s tutorial is a part of our definitive guide on Excel Formulas.
Abbreviate names using the ABBREVIATE function
The ABBREVIATE function returns the abbreviation for the supplied string. User-defined functions provide huge advantages when you are working with string manipulation formulas.
Syntax:
=ABBREVIATE(cell_reference)Arguments:
The function uses one required argument; the cell reference.
In the example shown, the formula in D3 is:
=ABBREVIATE(B3)
To implement this user-defined function into your spreadsheet, do the following:
- Use the Alt + F11 keyboard shortcut to open the VBA Editor
- Right-click on the ThisWorkbook
- Add a new module
- Copy and insert the snippet
- Save your workbook as a .xlsm file.
Here is the code:
Function Abbreviate(strC As String) As String
Dim Company() As String
Dim i As Byte, j As Byte
Dim strAbbr As String
Company() = Split(strC, " ")
i = UBound(Company())
If i > 0 Then
For j = 0 To i
strAbbr = strAbbr & UCase(Left(Company(j), 1))
Next j
Else
strAbbr = strC
End If
Abbreviate = strAbbr
End Function
The next example below will use an advanced formula to extract the initials from names or create acronyms.
To speed up your work, download our DataFX add-in to expand Excel’s built-in library with user-defined functions.
Generic Formula to Abbreviate names
Let us see how to check when a cell contains specific text using a simple combination of regular excel functions. It looks a bit complex, but try to use it if you are a function geek.
=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
- The INDIRECT function has a parameter: numbers between 65 to 90. This range corresponds to the ASCII codes for all capital letters between A-Z.
- We use MID, ROW, INDIRECT, and LEN functions to convert a string to letters.
- TEXTJOIN will concatenate the extracted letters into one word.
Special requirement before applying the formula: The source text must include capitalized words.
