How to Convert Abbreviations to Words in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 2 simple methods to convert abbreviations to words in Excel. You can quickly use these methods even in large datasets to get the full form of any abbreviations. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.


How to Convert Abbreviations to Words in Excel: 2 Simple Methods

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 3 unique columns which are Employee Name, Age, and Country. Although we may vary the number of columns later on if that is needed.

convert abbreviations to words in excel


1. Using Excel Proofing to Convert Abbreviations to Words

The Proofing tool in Excel gives some powerful options to the users. We can perform spell-check, search words with similar meanings, etc. In this method, we shall see how to use this tool to convert abbreviations to words. Follow the steps below to do this.

Steps:

  • First, click on File at the top of the screen.

proofing to convert abbreviations to words in Excel

  • Next, click on Option from the left panel.

  • Now in the Excel Options window, select Proofing and click on AutoCorrect Options.

  • Then, enter the abbreviation in the Replace field and the full word in the With field as in the image below.
  • After that, click on Add and similarly add other abbreviations which you would require.

  • Next, go to cell D5 and type CAN as the abbreviation you set in the proofing window.

  • Then, press the Enter key and this will convert the abbreviation into a complete word denoting the country name.

  • Similarly, type in the abbreviations of the remaining countries and this should work for all of them.

Read More: How to Find 2 Letter State Abbreviations in Excel


2. Applying VLOOKUP Function

The VLOOKUP function in Excel gives us the option to look up vertically in a data table. Let us see how to apply this function to convert abbreviations to words.

Steps:

  • To begin with, create a worksheet with the name VLOOKUP1, and in it insert all the abbreviations with the detailed word form.

vlookup function to convert abbreviations to words in Excel

  • Now, go to another worksheet and enter the following formula in cell E5:
=VLOOKUP(D5,VLOOKUP1!$B$5:$C$10,2,0)

Here, cell D5 is the lookup_value, VLOOKUP1!$B$5:$C$10 is the table_array and 2 is the column_index_num parameter.

  • Next, press Enter and this should show the complete word form of the abbreviation in cell D5.

vlookup function to convert abbreviations to words in Excel

  • Finally, copy this formula down using Fill Handle and you should get the complete form of all the country names.

Read More: How to Use VLOOKUP to Find State Abbreviations in Excel


Things to Remember

  • If the range-lookup parameter is TRUE, the VLOOKUP function will perform an approximate match.
  • If the table array is not entered correctly, this function might give a #N/A error which is very common for this function.
  • If you add a new column to the original table, the result of the VLOOKUP formula will not update. So keep this in mind.
  • The AutoCorrect Option is also available in other MS Office applications.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to convert abbreviations to words in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo