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**:

`=LEFT(B5,1)&". "&MID(B5,FIND(" ",B5)+1,LEN(B5))`

- Press
**Enter**and use**Fill Handle**.

**Formula Breakdown**

**LEFT(B5,1):****The LEFT function**extracts the first character (initial) from the text in cell**B5**.**“. “:**This is a period (dot) placed after the initial. It adds a period and a space to separate the initial from the rest of the name.**MID(B5, FIND(” “,B5)+1, LEN(B5)):**This section extracts the portion of the name after the space.**FIND(” “,B5)+1: The FIND function**locates the position of the first space in the text. Adding 1 to this position gives the starting point of the name (after the space).**LEN(B5):****The LEN function**calculates the total length of the text in cell**B5**.**MID****(…):****The MID function**extracts a portion of the text starting from the position calculated above, and its length extends until the end of the text.

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

- Type the following formula in cell
**C5**:

`=CONCATENATE(LEFT(B5,1),". ",MID(B5,FIND(" ",B5)+1,LEN(B5)))`

- Press
**Enter**and use**Fill Handle**for the complete result.

**Formula Breakdown**

**LEFT(B5,1):**This extracts the first character (initial) from the text in cell**B5**.**“. “:**This part adds a period and a space to separate the initial from the rest of the name.**MID(B5, FIND(” “,B5)+1, LEN(B5)):**This part extracts the portion of the name after the space.**FIND(” “,B5)+1:**The**FIND**function locates the position of the first space in the text. Adding 1 to this position gives the starting letter of the name (after the space).**LEN(B5):**The**LEN**function calculates the total length of the text in cell**B5**.**MID(…):**The**MID**function extracts a portion of the text starting from the position calculated above, and its length extends until the end of the text.**CONCATENATE(…): The CONCATENATE function**combines the results of the previous steps into a single string.

## 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**:

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

- Press
**Enter**and use**Fill Handle**as before.

**Formula Breakdown**

**LEN(B5) –**The**LEN**function calculates text length in**B5**.**ROW(INDIRECT(“1:”&LEN(B5))) –**This construct generates an array of sequential numbers from 1 to the length of the text in**B5**. It is created using**the ROW function**and**the INDIRECT function**, which converts the range string “1:” concatenated with the length of**B5**into an actual range.**MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1) –**The**MID**function extracts individual characters from cell**B5**. It takes three arguments: the text to extract from (**B5**), the starting position (generated by**ROW(INDIRECT(“1:”&LEN(B5))))**, and the number of characters to extract (**1**). This allows the formula to extract each character of the text in**B5**.**CODE(MID(…)) –****The CODE function**converts each extracted character into its corresponding Unicode value. It is applied to the result obtained from the**MID**function.**ROW(INDIRECT(“63:90”)) –**This construct generates an array of numbers from 63 to 90. These numbers represent the Unicode values for uppercase letters in the ASCII character set.**MATCH(CODE(…),ROW(INDIRECT(“63:90”)),0) – The MATCH function**checks if each Unicode value obtained in step 4 is present within the array of uppercase letter Unicode values from step 5. It returns the position of the match or an error if there is no match. The value of 0 as the third argument ensures an exact match.**ISNUMBER(MATCH(…)) – The ISNUMBER function**checks if the result of the**MATCH**function in step 6 is a number. If there is a match, it returns TRUE; otherwise, it returns FALSE.**IF(ISNUMBER(…),MID(…),””) –****The IF function**evaluates the result from step 7. If it is TRUE (meaning there was a match), the**MID**function is used again to extract the character from B5. Otherwise, an empty string (” “) is returned.**TEXTJOIN(“”,1,IF(…)) – The TEXTJOIN function**combines all the non-empty characters obtained in step 8 into a single text string. The separator argument is an empty string (” “), and the ignore_empty argument is set to 1 to exclude empty values.

**TEXTJOIN**function is available in Microsoft Office 365 and Excel 2019.

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

- Put the following formula inside cell
**C5**:

`=UPPER(TRIM(LEFT(B5,1)&MID(B5,FIND(" ",B5&" ")+1,1)&MID(B5, FIND("*", SUBSTITUTE(B5&" "," ","*",2))+1,1)))`

- Press
**Enter**and use the**Fill Handle**appropriately.

**Formula Breakdown**

**LEFT(B5,1) –**The**LEFT**function extracts the first character from cell**B5**. It takes two arguments: the text to extract from (B5) and the number of characters to extract (1). This captures the first letter of the text.**MID(B5,FIND(” “,B5&” “)+1,1) –**The**MID**function extracts a single character from within a text string. It takes three arguments: the text to extract from (B5), the starting position (obtained by finding the first space using**FIND**), and the number of characters to extract (1). This captures the character immediately following the first space.**MID(B5, FIND(“*”, SUBSTITUTE(B5&” “,” “,”*”,2))+1,1) –**This is similar to the previous**MID**It extracts a single character from within a text string. The starting position is obtained by finding the second occurrence of a space (replaced by an asterisk using**the SUBSTITUTE function**) and adding 1. This captures the character immediately following the second space.**TRIM(…) –****The TRIM function**removes any extra spaces before and after the extracted characters. It ensures that the resulting abbreviation does not have leading or trailing spaces.**UPPER(…) – The UPPER function**converts the resulting abbreviation to uppercase. It ensures consistent capitalization for all abbreviations.

### 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
```

**Code Breakdown**

- This VBA code defines a function called
**AbbreviateString**that takes a string (strC) as input and returns an abbreviated version of the string. - The function begins by declaring variables, including an array
**Text()**to store the individual words of the string and x and y as counters. - The Split function is used to split the input string into an array of words, using space (” “) as the delimiter. The result is stored in the Text() array.
- The variable x is assigned the upper bound of the Text() array, which represents the number of words in the string.
- If there is more than one word (x > 0), the code enters a loop from 0 to x and appends the uppercase first letter of each word to the
**strAbbr**string variable. - If there is only one word in the string (x = 0), the strAbbr variable is assigned the value of the input string itself.
- Finally, the function returns the strAbbr string, which contains the abbreviated version of the input string.

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

`=AbbreviateString(B5)`

- Press
**Enter**and use**Fill Handle**.

Thus we get Excel abbreviation using **VBA**.

**AbbreviateString()**is the User Defined Function that we have created using VBA beforehand.

## 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:

`=TEXT(D5, “ddd”)`

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

`=CHOOSE(WEEKDAY(D5),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")`

- Press
**ENTER**. Use**Fill Handle**.

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

**Formula Breakdown**

**WEEKDAY(D5):**Calculates the weekday of the date in cell**D5**.**The WEEKDAY function**returns a number representing the day of the week, where Sunday is 1, Monday is 2, Tuesday is 3, and so on.**CHOOSE(…):****The CHOOSE function**is used to select a value from a list of choices based on a specified index number. In this case, the index number is the result of the**WEEKDAY**function (the day of the week).**“Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”:**These are the choices or values that the**CHOOSE**function can select from. Each corresponds to a day of the week in order.

**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:**

`0,"K"`

- 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:**

`0.0,,"M"`

- 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:**

`0.0,,,"B"`

- 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:**

`[>999999999.999]0.0,,,"B";[>999999.999]0.0,,"M";0,"K"`

- 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**:

`=VLOOKUP(B14,B4:C11,2,FALSE)`

- 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**:

`=XLOOKUP(B14,B5:B11,C5:C11,"Not Available",0,)`

- 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**:

`=INDEX(C5:C11,MATCH(B14,B5:B11,0),)`

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