Microsoft Office Tutorials and References
In Depth Information
Extracting middle names
Extracting middle names
Some (but not all) of the presidents on the list have a middle name. Can Flash Fill extract the middle
The answer: Sort of. I provided several examples of middle names, and Flash Fill successfully
extracted the other middle names. But for names without a middle name, it extracted the first name.
No matter what I tried, I could not get Flash Fill to ignore names that had no middle name.
Extracting domain names from URLs
Here’s another example of using Flash Fill. Say you have a list of URLs and need to extract the
filename (the text that follows the last slash character).
Figure 64-4 shows a list of URLs. Flash Fill required just one example of a filename entered in column
B. I pressed Ctrl+E, and Excel filled in the remaining rows. Flash fill worked equally well removing the
filename from the URL, in column C.
Figure 64-4: Flash Fill extracted the filenames from URLs.
Potential problems
Flash Fill is a great feature, but if you use it for important data, you should be aware of some
potential problems:
Sometimes it just doesn’t work. Extracting middle names seems like a simple pattern, but
Flash Fill was not capable of recognizing the pattern.
It’s not always accurate. With a small set of data, it’s usually easy to check to ensure that
Flash Fill worked as you intended it to work. But if you use Flash Fill on thousands of rows of
data, you can’t be assured that it worked perfectly unless you examine every row. Flash Fill
works best with data that is very consistent.
It’s not dynamic. If you change any of the information that Flash Fill used, the changes are
not reflected in the filled column.
There is no “audit trail.” If you use formulas to extract data, the formulas provide
documentation so anyone can figure out how the data was extracted. Using Flash Fill, on the other
hand, provides no such audit trail. There is no way to see which rules Excel used to extract
the data.
Search JabSto ::

Custom Search