# Abbreviate names or words

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)``

• Use the Alt + F11 keyboard shortcut to open the VBA Editor
• Right-click on the ThisWorkbook
• 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.

## 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.

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.