Md. Araf Bin Jayed
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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
Read More: How to Convert Abbreviations to Words in Excel (2 Easy Methods)
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.
Read More: How to Find 2 Letter State Abbreviations in Excel (4 Ways)
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.
Read More: How to Convert State Abbreviation to Name in Excel (3 Easy Ways)
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.
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.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
