How to Apply Abbreviation Using Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

3 Easy Methods to Apply Abbreviation Using Formula in Excel


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),""))

Combining Various Functions as An Easy Method to Apply Abbreviation Using Formula in Excel

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

Merging Multiple Functions as An Easy Method to Apply Abbreviation Using Formula in Excel

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.

Writing VBA Code for Applying Custom Function as An Easy Method to Apply Abbreviation Using Formula in Excel

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo