Abbreviate names or words

Abbreviate names or words

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)
The Excel Abbreviate function

To implement this user-defined function into your spreadsheet, do the following:

  1. Just press Alt + F11 to open the VBA Editor
  2. Right-click on the ThisWorkbook
  3. Add a new module
  4. Copy and Insert the snippet
  5. 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

datafx user defined function library

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.

Formula based example for abbreviate
=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.

Aditional resources