Abbreviate names or words

how-to-abbreviate-names-or-words-in-Excel

Learn how to abbreviate names or words using regular Excel functions or the ABBREVIATE user-defined function.

First, we will introduce the ABBREVIATE function, using 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.

How to abbreviate names in Excel?

Here are the steps to abbreviate names or words in Excel:

  1. Open Excel
  2. Type =ABBREVIATE(B1)
  3. Press Enter.
  4. The formula returns the abbreviated name from the specified cell.

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.

formula-example

In the example shown, the formula in D3 is:

=ABBREVIATE(B3)

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

  1. Use the Alt + F11 keyboard shortcut 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 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 uses an advanced formula to extract the initials from names or create acronyms. To speed up your work, download our DataFX add-in, which expands Excel’s built-in library with user-defined functions.

Using regular functions

Let’s see how to check when a cell contains specific text using a simple combination of regular Excel functions. It looks 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),””))

Abbreviate-names-or-words-using-regular-formulas

Okay, please take a closer look at the formula and evaluate it from the inside out.

  1. The INDIRECT function has a parameter: numbers between 65 and 90. This range corresponds to the ASCII codes for all capital letters between A and Z.
  2. We use MID, ROW, INDIRECT, and LEN functions to convert a string to letters.
  3. TEXTJOIN will concatenate the extracted letters into one word.

Note: A special requirement before applying the formula is that the source text must include capitalized words.