Microsoft Office Tutorials and References
In Depth Information
consistent. You could write an array formula, but it would be very complicated. Another option is to write a cus-
tom worksheet function using VBA. This might be a good job for Flash Fill.
Figure 16-9: The goal is to extract the numbers in column A.
To try using Flash Fill, activate cell B1 and type the first number (20). Move to B2 and type the second number
(6). Can Flash Fill identify the remaining numbers and fill them in? Choose Data ⇒ Data Tools ⇒ Flash Fill (or
press Ctrl+E), and Excel fills in the remaining cells in a flash. Figure 16-10 shows the result.
It looks good. Excel somehow managed to extract the numbers from the text. Examine the results more closely,
though, and you see that it failed for numbers that include decimal points. Accuracy increases if you provide
more examples — such as an example of a decimal number. Delete the suggested values, enter 3.12 in cell B6,
and press Ctrl+E. This time, Excel gets them all correct (see Figure 16-11).