If the original list changes, the “fixed” version of the list does not update. The disadvantage is that there is no dynamic connection back to the original list of text. 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. Pick the one that makes the most sense to your brain. We can write the formula two different ways.Įither version produces the desired results. Suppose we wish to convert the text to upper case and trim all the extraneous spaces. We can combine these functions to both trim and fix text casing. In our sample file, we will select cell E5 and enter the following formula:įill the formula down column E to finish converting the list in column A. = TRIM(“ This is a test of the TRIM function ”) The syntax for the TRIM function is as follows: If you need to remove any unnecessary leading spaces, trailing spaces, or multiple spaces in between words, you can use the TRIM function. Notice in our solution columns ( B:D), “James Willard” has an extra space between his first and last names.Ī less obvious issue is that “Gary Miller” has an extra space at the end of his name. The choice we want is labeled “ Copy Here as Values Only”. This presents you with a menu of choices.

drag a small amount away form the selection and then immediately return to the original selection location.

using your RIGHT mouse button, right-click on the thick, green border surrounding the selection.

highlight the desired cells to be converted.

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.Ī lesser-know technique to converting formulas to the formula’s results is to do the following: In our sample file, we will select cell B5 and enter the following formula:įill the formula down column B to finish converting the list in column A. In most cases, the cell reference version is the most useful option of the two. =UPPER(“This is a test of the upper function”) The variable “text” can refer to a cell address or to a statically declared string. The syntax for the UPPER function is as follows: The function to convert any cell’s text to upper case is known as the UPPER function.

Excel shortcut keys capital letters