Microsoft Office Tutorials and References
In Depth Information
Figure 16-13: Using Flash Fill to extracts filenames from URLs.
Excel's new Flash Fill feature is an interesting idea, but the main limitation is that (unlike formulas) it's not a
dynamic technique. If your data changes, the flash-filled column does not update.
Flash Fill seems to work reliably if the data is very consistent, but it's still a good idea to examine the results
carefully. And think twice before trusting Flash Fill with important data. There's no way do document how the
data was extracted. You just have to trust Excel.
You can also use the Flash Fill feature to create new data from multiple columns. Just
provide a few examples of how you want the data combined, and Excel will figure out
the pattern and fill in the column.
Changing the case of text
Often, you'll want to make text in a column consistent, in terms of case. Excel provides no direct way to change
the case of text, but it's easy to do with formulas. See the sidebar, “Transforming data with formulas.”
The three relevant functions are
• UPPER converts the text to ALL UPPERCASE.
• LOWER converts the text to all lowercase.
• PROPER converts the text to Proper Case (the first letter in each word is capitalized, as in a proper name).
Transforming data with formulas
Many of the data clean-up examples in this chapter describe how to use formulas and functions to transform data
in some way. For example, you can use the UPPER function to transform text into uppercase. When the data is
transformed, you'll have two columns: the original data and the transformed data. Almost always, you'll want to re-
place the original data with the transformed data. Here's how:
1. Insert a new temporary column for formulas to transform the original data.