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

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.

```
=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.