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

**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**

- Use DataFx, our
**free excel add-in** - Download the tool
**Follow the add-in installation steps**

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

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