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.
The function uses one required argument; the cell reference.
In the example shown, the formula in D3 is:
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.
- 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.