In this tutorial, we will be covering the various ways in which you can change the case of text in Excel. We will go over how to change the case of text using Excel’s built-in functions as well as how to use formulas to change the case of text. Additionally, we will also cover how to change the case of text in multiple cells at once.
Making all characters lower case. In this example, we have a set of names that we want to change all the letters to lowercase. To do this, we will use the LOWER function. The LOWER function in Excel is a built-in function that is used to convert text to lowercase. Here is a step-by-step guide on how to use the LOWER function in Excel:
- Select a cell you want to show your result.
- In the cell, Type “=LOWER(” (without the quotes) and click on the cell you want to convert to lowercase. In this case, A2.
- Type a closing parenthesis “)” and press Enter. The selected cell will now display the text in lowercase.
If you want to convert multiple cells to lowercase, you can simply drag the formula to other cells or copy and paste the formula to other cells.
Making all characters upper case. Using the same example, we want to change the names so that all letters are uppercase. To do this, we will use the UPPER function. The UPPER function in Excel is a built-in function that is used to convert text to uppercase. Here is a step-by-step guide on how to use the UPPER function in Excel:
- Select a cell you want to show your result.
- In the cell, Type “=UPPER(” (without the quotes) and click on the cell you want to convert to lowercase. In this case, A2.
- Type a closing parenthesis “)” and press Enter. The selected cell will now display the text in lowercase.
If you want to convert multiple cells to lowercase, you can simply drag the formula to other cells or copy and paste the formula to other cells.
Capitalizing only the first letter of the word. Using the same example, we want to format the names in proper case, which means having the first letter of each word in upper case. To do this, we will use the PROPER function. The PROPER function in Excel is a built-in function that is used to convert text to proper case. Here is a step-by-step guide on how to use the PROPER function in Excel:
- Select a cell you want to show your result.
- In the cell, Type “=PROPER(” (without the quotes) and click on the cell you want to convert to lowercase. In this case, A2.
- Type a closing parenthesis “)” and press Enter. The selected cell will now display the text in lowercase.
If you want to convert multiple cells to lowercase, you can simply drag the formula to other cells or copy and paste the formula to other cells.
Using Flash fill and it’s limitations. Alternatively, you can use flash fill to make the cases uniform based on the first cell in a column. To know more in using flash fill, you can check our tutorial.
Flash fill for all uppercase:
Flash fill for all lowercase:
However, the feature has its limitations. There may be some times where Excel does not recognize the pattern for the flash fill. Here are some examples:
Flash fill for proper casing:
In this situation, since the first cell in the A column is already in proper case, when trying to flash fill the B column in proper case, Excel thinks it’s just simply copying the value from the A column, so the values of Nancy raul and reema Sharma are copied on to the column.
To fix this, change the value of Nancy raul in the B column to proper case and use flash fill again.
Abbreviating the names using flash fill:
In this situation, Excel only recognizes the letters in uppercase as an abbreviation.
Even if correcting the abbreviation in the B column based on Nancy Raul, the flash fill will still be unreliable in doing this correctly:
Conclusion
Changing the case of text in Excel is a common task that can be accomplished in several ways. The LOWER, UPPER, and PROPER functions are built-in functions that can be used to change the case of text in Excel. Additionally, Flash Fill is another useful tool that allows you to quickly format text data without having to use formulas or functions. However, it has some limitations, and it’s not always able to recognize the pattern you’re trying to match. It’s best to use these tools in conjunction with other techniques to achieve the desired result. It’s also important to keep in mind the limitations of each tool and the complexity of the data you’re working with before deciding which method to use.
