DEVISING A SOLUTION
The thought process of finding a solution in Excel is interesting. Your first reaction might be to use a formula. For instance, you might choose to break the part number into three pieces using LEFT, MID, and RIGHT and then use the concatenation operator (&) to join everything back together. As shown in cell D4 of Figure 1, you end up with the rather lengthy formula of =LEFT(B4,3)&"."&MID(B4,4,2)&"."&RIGHT(B4,4).
But a more direct way might be to use a custom number format to insert the decimal places and pass that custom number format to the TEXT function. This method fails if you simply use “000.00.0000” as the custom number format code. As shown in cell C5, Excel can’t make sense of =TEXT(B5,”000.00.0000”).
Yet there must be a way to insert characters using custom number formats. After all, the Social Security Number format under Format Cells, Number, Special successfully inserts two hyphens in a nine-digit number. The formula in D6 uses =TEXT(B6,"000-00-0000") and generates 111-22-3333. Why does Excel allow a hyphen but not periods?
If you’re feeling clever, you can work around this limitation. Use TEXT to generate 111-22-3333, and then use SUBSTITUTE to change the hyphens to periods: =SUBSTITUTE(TEXT(B7,"000-00-0000”),"-","."). As shown in cell D7, the formula is almost as lengthy as using LEFT, MID, and RIGHT.
For me, many hours later, I started thinking about displaying numbers in thousands and millions. If you want to show numbers in thousands, you could use #,##0, as the custom number format (see cell D8). To add a “K” abbreviation, the code is #,##0,K, as shown in cell D9. Each comma after the last zero in the code divides the number by a thousand. And for millions, use the code #,##0,, shown in cell D10. But adding “M” as the abbreviation doesn’t work because “M” means months. The obscure solution? Use /M to insert an actual M in the number. The backslash tells Excel that the next single character should be inserted in the number. Would the same trick work if you used . in the code? As shown in cell D13, it works and is fairly short.
It’s likely that you have other formulas that would solve the problem, such as the one shown in D14 or others. But explaining one of these formulas to someone new to Excel will almost always yield a complaint of “Why does this have to be so complicated?”
FLASH FILL TO THE RESCUE
The impulse to use formulas is natural because you’ve been using formulas since you first learned Excel. But there’s another mature feature in Excel called Flash Fill. Introduced eight years ago in 2013, Flash Fill, found on the Data tab, expects you to type a sample of the answer that you’re looking for.
In Figure 2, a column of part numbers appears in cells N4:N9999. Type the desired output for the first part number. In this case, you would type 948.70.4598 in cell O4 to teach Excel the desired way to convert the number in cell N4.
Select the first blank cell. (In Figure 2, this would be cell O5.) The Flash Fill icon is a yellow lightning bolt over a 3x3 grid. It’s usually to the right of the Text to Columns and to the left of Consolidate. If it’s hard to locate, you can use Ctrl+E to invoke Flash Fill.
Press Ctrl+E or click on Flash Fill from cell O5. Excel will quickly follow your example. Every entry in column N will now have a corresponding entry in column O.
TIPS FOR USING FLASH FILL
Flash Fill tries to fill all cells in the current contiguous region of data. Always remember to type a heading above the new column (O3 in Figure 2) so Flash Fill doesn’t try to fill O3 with a value of ID.Number. Having a heading above the existing column and the new column will help the Flash Fill algorithm to know to ignore the headings and look for the patterns.
What if a few numbers are only eight characters instead of nine? Before selecting Flash Fill, type in examples of how to format each. When you invoke Flash Fill, it has a chance to discover the pattern and apply it correctly to the various length numbers.
Once you accept the Flash Fill results and begin working elsewhere in the worksheet, the new values are simply values. If something changes in column N, the corresponding value in column O won’t update.
SF SAYS
With Flash Fill results, you can copy and paste over the original data. With formulas, you would have to Paste Special Values.
February 2021
