Nazmul Hossain Shovon
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.
Download Practice Workbook
You can download the practice workbook from here.
2 Simple Methods to Convert Abbreviations to Words in Excel
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.
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.
- 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 (4 Ways)
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.
- 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.
- 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
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.
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. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
