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!
Special requirement before applying the function: The source text must include capitalized words.
In the example shown, the formula in B2 is:
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
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.
- 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.