While working in Excel, users often need to abbreviate text. The texts can be the name of a person, company, or organization. But there is no single function formula that can help them to abbreviate text. In this article, I will show you how to apply the abbreviation using formula in Excel.

## 3 Easy Methods to Apply Abbreviation Using Formula in Excel

In this article, you will see three methods to apply the abbreviation using formula in Excel. In the first method, you will see the combination of various Excel functions for abbreviating a cell value that contains a string of text. For the second method, I will merge some Excel functions for the same purpose. Lastly, I will use a custom function made from **VBA** code to do the same.

To illustrate my article further, I will use the following sample data set.

### 1. Combine TEXTJOIN, ISNUMBER, MATCH, CODE, MID, ROW, INDIRECT, LEN, and ROW Functions

In the first method, I will combine several Excel functions, which are the **TEXTJOIN**, **ISNUMBER**, **MATCH**, **CODE**, **MID**, **ROW**, **INDIRECT**, and **LEN** functions. By combining these functions, I will implement a formula that will help me to abbreviate a series of strings. You can learn more about this procedure in the following steps.

**Steps:**

- First of all, make an extra column beside the primary data set to show the result after the abbreviation.

- Secondly, insert the following combination formula in cell
**C5**to abbreviate the text of cell**B5**.

`=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)),ROW(INDIRECT("63:90")),0)),MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),""))`

**Formula Breakdown**

**=TEXTJOIN(“”,1,IF(ISNUMBER(MATCH(CODE(MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1)),ROW(INDIRECT(“63:90”)),0)),MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1),””))**

- Here,
**the TEXTJOIN function**joins the text values including any delimiter. - Then, the
**MID**,**ROW**,**INDIRECT**, and**LEN**functions convert the desired text string into a form of arrays or letters. - After that,
**the CODE function**gives outputs that are an array of numeric ASCII codes, which are one type of code for each of the letters from the previous conversion. - Moreover, the
**ROW(INDIRECT(“63:90”)**will show the ASCII codes for all the capital letters from A to Z. - Finally, the
**ISNUMBER**,**IF**, and**MATCH**functions will filter the results from the previous steps for the final abbreviation.

- Thirdly, press
**Enter**and then drag the formula to the lower cells of the column using**AutoFill**to see the desired results.

### 2. Merge UPPER, TRIM, LEFT, MID, FIND, and SUBSTITUTE Functions

The second method of this procedure will contain the merging of multiple functions. The merging will include the use of the **UPPER**, **TRIM**, **LEFT**,** MID**, **FIND**, and **SUBSTITUTE** functions. To see the application of this merged functions formula, go through the following steps.

**Steps:**

- Firstly, in cell
**C5**, type the following formula of the merged function.

`=UPPER(TRIM(LEFT(B5,1)&MID(B5,FIND(" ",B5&" ")+1,1)&MID(B5,FIND("*",SUBSTITUTE(B5&" "," ","*",2))+1,1)))`

**Formula Breakdown**

**=UPPER(TRIM(LEFT(B5,1)&MID(B5,FIND(” “,B5&” “)+1,1)&MID(B5,FIND(“*”,SUBSTITUTE(B5&” “,” “,”*”,2))+1,1)))**

- Firstly,
**the TRIM function**helps to remove any extra space from the given string. - Then,
**the LEFT function**will extract the first letter of the string from the previous step. - After that,
**the MID function**and**the FIND function**will find the first letter of the second text of the given string. - Then, the merged
**SUBSTITUTE**function will extract the first letter of the third or final text of the string.

- Secondly, press
**Enter**to see the abbreviated form of the text in cell**B5**. - Consequently, drag the
**Fill Handle**to drag the formula to the lower cells of the column.

### 3. Apply Custom Functions Using VBA Code

This is the last section of this article. In this method, I will use a custom function that is made from **VBA** code. After you make it, this function will help you abbreviate words. To learn more about this procedure, see the following steps.

**Steps:**

- First of all, to write the code, go to the
**Developer**tab of the ribbon and select**Visual Basic**.

- Secondly, from the Insert tab of the
**VBA**window, choose**Visual Basic**.

- Thirdly, copy and paste the following code into the module.

```
Function AbbreviateString(strC As String) As String
Dim Text() As String
Dim x As Byte, y As Byte
Dim strAbbr As String
Text() = Split(strC, " ")
x = UBound(Text())
If x > 0 Then
For y = 0 To x
strAbbr = strAbbr & UCase(Left(Text(y), 1))
Next y
Else
strAbbr = strC
End If
AbbreviateString = strAbbr
End Function
```

- Here, I have named the custom function as AbbreviateString.

- Fourthly, save the code and go back to the worksheet.

- Afterward, type ABB in cell
**C5**and you will see the suggestion of the newly created custom function. - Then press
**Tab**or double-click on the suggestion for using the function.

- After that, use the following formula in cell C5.

`=AbbreviateString(B5)`

- Finally, press
**Enter**and to see the result for the whole resultant column use**AutoFill**.

## Things to Remember

- Remember to save the Excel file as an
**xlsm**file for enabling the macro code. - From the result, you can see that the second method will only abbreviate the first three words. So, if you have more than four words for abbreviation, use the first or third method.
- If you’re not using Microsoft 365 then press
**Ctrl + Shift + Enter**instead of only**Enter**to get results from an array formula.

**Download Practice Workbook**

You can download the free Excel workbook here and practice on your own.

## Conclusion

Thatâ€™s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply the abbreviation using formula in Excel. Please share any further queries or recommendations with us in the comments section below.

**<< Go Back to Excel Abbreviation | Learn ExcelÂ **