How to Apply Abbreviation in Excel (Abbreviate Words, Numbers)

In this Excel tutorial, you will learn how to,
– Create an abbreviation for names or words using an initialism approach, where only the first word in a group is abbreviated
– Apply abbreviations to all words in a group using Excel functions and VBA codes
– Apply abbreviation to days
– Abbreviate numbers
– Find the corresponding abbreviation from a given list

I have used Microsoft 365 to show you these Excel abbreviation techniques for this tutorial.

Abbreviation in Excel improves data entry efficiency by reducing typing efforts. In report generation, it helps to create short and space-saving reports. Abbreviations help in summarizing data and make lengthy text or descriptions easier to explain.

In the following overview image, we have combined multiple functions to abbreviate the first word.

⏷Apply Abbreviation for Initial Word
 ⏵Combine LEFT, MID, FIND & LEN Functions
 ⏵Combine CONCATENATE, LEFT, MID, FIND & LEN Functions
⏷Apply Abbreviations for All Words
 ⏵Combine Multiple Functions to Abbreviate All Words (Microsoft 365 and Excel 2019)
 ⏵Combine Multiple Functions to Abbreviate All Words (All Excel Versions)
 ⏵Apply Custom VBA Function to Abbreviate All Words
⏷Abbreviate Days in Excel
 ⏵Use TEXT Function
 ⏵Combine CHOOSE & WEEKDAY Functions
⏷Apply Abbreviation for Numbers
 ⏵Abbreviate Numbers in Thousands
 ⏵Abbreviate Numbers in Millions
 ⏵Abbreviate Numbers in Billions
 ⏵Abbreviate Numbers in Billions, Millions, or Thousands According to Value
⏷Find Corresponding Abbreviation from a List
 ⏵Use VLOOKUP Function
 ⏵Use XLOOKUP Function
 ⏵Combine INDEX & MATCH Functions
⏷Things to Remember
⏷Frequently Asked Questions
⏷Excel Abbreviation: Knowledge Hub

How to Apply Abbreviation for Initial Word in Excel

Initialism format of abbreviation means a special type of abbreviation where only the first word among a group of words is abbreviated. For example: Walmart Inc. >> W. Inc.

We can abbreviate a group of multiple words in initialism format in 2 ways using Excel functions.

1. Combining LEFT, MID, FIND & LEN Functions to Abbreviate in Excel

• Write the following formula in C5:

• Press Enter and use Fill Handle.

2. Combining CONCATENATE, LEFT, MID, FIND & LEN Functions for Abbreviation

• Type the following formula in cell C5:

• Press Enter and use Fill Handle for the complete result.

How to Apply Abbreviations for All Words in Excel

We can apply abbreviations to all the words in a word group in 3 ways.

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

• Put the following formula in cell C5:

• Press Enter and use Fill Handle as before.

2. Combining UPPER, TRIM, LEFT, MID, FIND, and SUBSTITUTE Functions for Abbreviation

• Put the following formula inside cell C5:

• Press Enter and use the Fill Handle appropriately.

3. Applying Custom Function Using VBA

• First, click the Developer tab >> select Visual Basic.

• Then, select Insert >> Module.

If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon Excel.

• Write the following code inside Module1:

Code Syntax:

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

• Return to the Custom sheet. We’ll use the following formula in C5:

• Press Enter and use Fill Handle.

Thus we get Excel abbreviation using VBA.

How to Abbreviate Days in Excel

Suppose, we have the founding dates of the companies. Now, we want to get the corresponding abbreviated weekdays associated with the dates. We can achieve that following the 2 methods below.

1. Using TEXT Function

• Inside cell E5, type the following formula:

• Press ENTER >> use Fill Handle.

You will get corresponding abbreviated day names for all the founding dates.

2. Combining CHOOSE & WEEKDAY Functions

• Inside cell E5, type the following formula:

• Press ENTER. Use Fill Handle.

You will get corresponding abbreviated day names for all the founding dates.

Read More: How to Abbreviate Days of the Week in Excel

How to Apply Abbreviation for Numbers in Excel

1. Abbreviating Numbers in Thousands (K)

Suppose, we have the number of workforces for each of the companies. Now, we want to abbreviate these numbers into thousands (K). To do that,

• Select the range D5:D11 >> click on Home >> Small Arrow (Number group).
• A dialogue box named Format Cells will open. Select Number >> Custom, and type the following inside the Type:

• Click on OK.

• Lastly, copy the numbers from range C5:C11 and paste them inside range D5:D11.

All the numbers are abbreviated into thousands as per the custom format.

2. Abbreviating Numbers in Millions (M)

Suppose, we have the salaries of the CEOs for each of the companies. Now, we want to abbreviate these salaries into millions (M). To do that,

• Select the range D5:D11 >> click on Home >> Small Arrow (Number group).
• A dialogue box named Format Cells will open. Select Number >> Custom, and type the following inside the Type:

• Click on OK.

• Lastly, copy the numbers from range C5:C11 and paste them inside range D5:D11.

All the numbers are abbreviated into millions as per the custom format.

3. Abbreviating Numbers in Billions (B)

Suppose, we have yearly revenues for each of the companies. Now, we want to abbreviate these revenues into billions (B). To do that,

• Select the range D5:D11 >> click on Home >> Small Arrow (Number group).
• A dialogue box named Format Cells will open. Select Number >> Custom, and type the following inside the Type:

• Click on OK.

• Lastly, copy the numbers from range C5:C11 and paste them inside range D5:D11.

All the numbers are abbreviated into billions as per the custom format.

4. Abbreviating Numbers in Billions, Millions or Thousands According to Value

Now let us unlock the full power of custom number formatting. We can convert large figures into billions, smaller ones into millions, and even smaller values into thousands, all within a single formula with the help of custom number formatting. To do that,

• First, select the range E5:E25 >> click on Home >> Small Arrow (Number group).
• A dialogue box named Format Cells will open. Select Number >> Custom, and type the following inside the Type:

• Click on OK.

• Lastly, copy the numbers from range D5:D25 and paste them inside range E5:E25.

All the numbers are abbreviated into billions, millions, or thousands as per the custom format.

Read More: How to Abbreviate Numbers in Excel

How to Find Corresponding Abbreviation from a List in Excel

1. Using VLOOKUP Function

Suppose, we have a list of 8 companies with their corresponding abbreviated names. Now we want to find the correct abbreviation for a company name using the VLOOKUP function. To do that,

• Inside cell B14, type the full name of the company for which we are searching the abbreviated name.
• Write the following formula inside cell C14:

• Press ENTER.

You will get the corresponding abbreviated name.

2. Using XLOOKUP Function

Now we want to find the correct abbreviation for a company name using the XLOOKUP function. To do that,

• Inside cell B14, type the full name of the company.
• Write the following formula inside cell C14:

• Press ENTER.

You will get the corresponding abbreviated name. And if you type a name inside cell B14 that is not on the list, the formula returns “Not Available” inside cell C14.

3. Combining INDEX & MATCH Functions

We’ll find the abbreviations using the combination of INDEX & MATCH functions in this method. To do that,

• Inside cell B14, type the full name of the company.
• Write the following formula inside cell C14:

• Press ENTER.

You will get the corresponding abbreviated name.

What Are the Things to Remember?

• Validate the data and ensure that the abbreviations accurately represent the original information. Double-check for any potential errors or inconsistencies.
• Maintain consistency in your abbreviation approach throughout the spreadsheet or workbook. Use the same abbreviations for the same terms or phrases to avoid confusion.
• Document the abbreviations used, either in a separate key or as a comment within the cell, to help others understand the meaning behind the abbreviations.

Download Practice Workbook

Conclusion

By going through this article, I hope, you have learned the techniques of using built-in functions, or a User-Defined Function (UDF), and custom formatting to abbreviate in Excel. Uncover practical use cases where Excel abbreviation plays a role in simplifying and presenting data more efficiently.

With an Abbreviation in Excel, we can simplify tasks by using shorthand notations for words and phrases, boosting efficiency in data entry and formulas. This reduces errors, saves time, and enhances overall productivity in spreadsheet tasks.

Frequently Asked Questions

1. How do I replace abbreviations in Excel

Answer: To replace an abbreviation in Excel, you can use the Find and Replace feature. Here’s how you can do it:

• Select the range of cells that contain the abbreviations you want to remove.
• Press Ctrl + H on your keyboard to open the Find and Replace dialog box.
• In the dialog box, enter the abbreviation you want to remove in the “Find what” field.
• Enter the elaboration that will replace the abbreviation inside the “Replace with” field.
• Click on the “Replace All” button to replace all occurrences of the abbreviation with the corresponding elaboration in the selected range.

2. How do I prevent Excel from abbreviating numbers

Answer: By placing an Apostrophe ( ‘ ) preceding the number, you will prevent Excel from reformatting the number and display the number exactly how you have it entered.

3. How do I stop Excel from abbreviating dates

Answer: If you want to prevent Excel from abbreviating dates, you can change the formatting of the cells to display the full date format. Here’s how you can do it:

• Select the cells containing the dates that you want to stop Excel from abbreviating.
• Right-click on the selected cells and choose “Format Cells” from the context menu.
• In the “Format Cells” dialog box, navigate to the “Number” tab.
• Select “Date” from the category list on the left.
• Choose the desired date format from the available options in the list on the right.
• Click on the “OK” button to apply the new date format to the selected cells.

Excel Abbreviation: Knowledge Hub

<< Go Back to Learn Excel