Method #1: Using Formulas
The main advantage to using formulas is that if the source data changes, the updated formula version automatically updates.
In our data set, we have a list of names with a variety of issues. Some names are lower case, some are upper case, some are proper case, and some are mixed up beyond all reason.
We will create a version of each name in the list to upper case, lower case, and proper case using formulas. Each of these methods are incredibly simple.
Upper Case
The function to convert any cell’s text to upper case is known as the UPPER function. The syntax for the UPPER function is as follows:
=UPPER(text)The variable “text” can refer to a cell address or to a statically declared string.
=UPPER(A1)or
=UPPER(“This is a test of the upper function”)In most cases, the cell reference version is the most useful option of the two.
In our sample file, we will select cell B5 and enter the following formula:
=UPPER(A5)
Fill the formula down column B to finish converting the list in column A.
If you don’t want the formulas in the resultant cells, you just want the new upper-cased versions of the names as if they had been hand-typed, you can select the names and perform a Copy -> Past Values operation on them.
A lesser-know technique to converting formulas to the formula’s results is to do the following:
- highlight the desired cells to be converted
- using your RIGHT mouse button, right-click on the thick, green border surrounding the selection
- drag a small amount away form the selection and then immediately return to the original selection location
- release your right mouse button
This presents you with a menu of choices. The choice we want is labeled “Copy Here as Values Only”.
Featured Course
Excel Essentials for the Real World
Lower Case
The function to convert any cell’s text to upper case is known as the LOWER function. The syntax for the LOWER function is as follows:
=LOWER(text)The variable “text” can refer to a cell address or to a statically declared string.
=LOWER(A1)or
=LOWER(“THIS IS A TEST OF THE LOWER FUNCTION”)As with the UPPER function, the cell reference version is the most useful option of the two.
In our sample file, we will select cell C5 and enter the following formula:
=LOWER(A5)
Fill the formula down column C to finish converting the list in column A.
Proper Case
The function to convert any cell’s text to upper case is known as the PROPER function. The syntax for the PROPER function is as follows:
=PROPER(text)The variable “text” can refer to a cell address or to a statically declared string.
=PROPER(A1)or
=PROPER(“THIS IS A TEST OF THE PROPER FUNCTION”)In our sample file, we will select cell D5 and enter the following formula:
=PROPER(A5)
Fill the formula down column D to finish converting the list in column A.
Bonus Problem to Solve
Notice in our solution columns (B:D), “James Willard” has an extra space between his first and last names.
A less obvious issue is that “Gary Miller” has an extra space at the end of his name.
If you need to remove any unnecessary leading spaces, trailing spaces, or multiple spaces in between words, you can use the TRIM function. The syntax for the TRIM function is as follows:
=TRIM(text)The variable “text” can refer to a cell address or to a statically declared string.
=TRIM(A1)or
= TRIM(“ This is a test of the TRIM function ”)In our sample file, we will select cell E5 and enter the following formula:
=TRIM(A5)
Fill the formula down column E to finish converting the list in column A.
We can combine these functions to both trim and fix text casing. Suppose we wish to convert the text to upper case and trim all the extraneous spaces. We can write the formula two different ways.
=UPPER(TRIM(A3))or
=TRIM(UPPER(A3))Either version produces the desired results. Pick the one that makes the most sense to your brain.
Method #2: Flash Fill
The advantage of Flash Fill is that it doesn’t require the use of functions and the result is like the Copy -> Paste Values action in that the result cells contain the text, not formulas.
The disadvantage is that there is no dynamic connection back to the original list of text. If the original list changes, the “fixed” version of the list does not update. This is okay if you have a static list or you only need to perform the conversion one time and you don’t require updates.
There are many ways to use Flash Fill, but a simple way is to type on the same row as the data a version of the data as you WISH it were formatted.
After you press ENTER to commit the new version to a cell, press the keyboard combination CTRL-E.
The system will look for patterns in what you typed against other text on the same row. If a pattern exists, such as “I see the text you typed, but you typed it in upper case letters”, the system will repeat the pattern for the remainder of the rows in the table.
The Flash Fill tool can also be activated by selecting Home (tab) -> Editing (group) -> Fill (button) -> Flash Fill.
Another way to activate the Flash Fill tool is to select Data (tab) -> Data Tools (group) -> Flash Fill.
If we use the Flash Fill technique to convert the names in column A to upper case version in column B, notice that “James Willard” still has the extra space between his names.
Flash Fill can fix that problem as well. Flash Fill can perform the TRIM and the UPPER functions simultaneously. The trick is to select a name that contains extra spaces before, after, or within itself. In this case, we will use “james willard”.
In cell B4, type “JAMES WILLARD” with only a single space separating the first from the last name.
After you press ENTER, press the keyboard combination CTRL-E.
Flash Fill will fix the names in BOTH directions of the list. Not only will it create upper case versions of the names, but it is smart enough to detect that you only placed a single space between the names and that it should do the same thing. Also, because you didn’t add any additional leading or training spaces, Flash Fill doesn’t place any in the results list of names.
Although Flash Fill is a fantastic tool that can quickly correct may data entry mishaps, it isn’t perfect. If there isn’t a recognizable pattern, or if it perceives multiple patterns, it may produce unexpected results. It’s always a good idea to double-check the output of Flash Fill for accuracy.
Method #3: ALL CAPS FONT
The advantage of this method is lack of composing any formulas or using Flash Fill.
Imagine a scenario where you always want a page title to be in upper case, but you don’t want to worry about how the user type the title. If the user enters the title in lower case, proper case, or sentence case, we want the typed text to automatically convert to upper case.
We can accomplish this by using a font that contains no lower case version of the letters.
When you are browsing through your list of fonts, you can tell if a font is upper case only because the font name will be in all upper case letters.
Some of the supplied fonts in Microsoft Windows/Office that contain only upper case letters are:
- Copperplate Gothic
- Engravers
- Felix Tilting
- Stencil
You are not restricted to fonts that came with Windows or Office. Many websites exist that provide both free and “pay-to-play” fonts.
When you are downloading a font from one of these sites, or other font supplier’s sites, be mindful that some fonts are free for personal use, but other fonts may require a fee when used in a business capacity.
When you download and install the font based on the website’s installation instructions, the font will be available to all your Windows and Office applications, not just Excel.
Using the Newly Installed Font
With the font installed, we return to Excel and select a cell where we will enter our title.
From the font dropdown list, select the desired font that contains all upper case letters.
It doesn’t matter whether you type your title in upper case, lower case, or mixed case, the result will always be in an upper case format.
Using Cell Styles to Expedite Font Assignment
Locating a specific font for all for all your titles can be time consuming, especially if you must repeatedly scroll through long list of font choices.
A timesaving way to apply an ALL CAPS font to a cell is to utilize Cell Styles.
Cell Styles are located on the Home tab in the Styles group.
You have the option to use an existing style, create your own style and add it to the library, or modify an existing style.
If we wish to use the Heading 1 style, but we wish it to be in all upper case letters, right-click on the Heading 1 style and select Modify.
In the Style dialog box, click the Format button.
In the Format Cells dialog box, select the Font tab and set the font to the desired ALL CAPS font. You can also use this opportunity to set the font color, underline color, border color, etc…
We can now select a cell and type in our new title. Once entered, with the title cell selected, click the Heading 1 style from the Cell Styles list.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
