We will use the following sample data set to apply abbreviations.
Method 1 – Combine TEXTJOIN, ISNUMBER, MATCH, CODE, MID, ROW, INDIRECT, LEN, and ROW Functions
Steps:
- Make an extra column after the primary data set to show the result after the abbreviation.
- 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),””))
The TEXTJOIN function joins the text values including any delimiter. The MID, ROW, INDIRECT, and LEN functions convert the desired text string into a form of arrays or letters. 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. The ROW(INDIRECT(“63:90”) will show the ASCII codes for all the capital letters from A to Z. The ISNUMBER, IF, and MATCH functions will filter the results from the previous steps for the final abbreviation.- Press Enter and then drag the formula to the lower cells of the column using AutoFill to see the desired results.
Method 2 – Merge UPPER, TRIM, LEFT, MID, FIND, and SUBSTITUTE Functions
Steps:
- In cell C5, insert 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)))
The TRIM function helps to remove any extra space from the given string. The LEFT function will extract the first letter of the string from the previous step. The MID function and the FIND function will find the first letter of the second text of the given string. The merged SUBSTITUTE function will extract the first letter of the third or final text of the string.- Press Enter to see the abbreviated form of the text in cell B5.
- Drag the Fill Handle to drag the formula to the lower cells of the column.
Method 3 – Apply Custom Functions Using VBA Code
Steps:
- To write the code, go to the Developer tab of the ribbon and select Visual Basic.
- From the Insert tab of the VBA window, choose Module.
- 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- We have named the custom function as AbbreviateString.
- Save the code and go back to the worksheet.
- Type ABB in cell C5 and you will see the suggestion of the newly created custom function.
- Press Tab or double-click on the suggestion for using the function.
- Use the following formula in cell C5.
=AbbreviateString(B5)
- Press Enter and, to see the result for the whole column, use AutoFill.
Things to Remember
- Save the Excel file as an xlsm file for enabling the macro code.
- The second method will only abbreviate the first three words.
- If you’re not using Microsoft 365, then press Ctrl + Shift + Enter instead of only Enter to get results from an array formula.
Download the Practice Workbook
<< Go Back to Excel Abbreviation | Learn Excel