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
