Excel Trick - How to show last 3 digits using Flash Fill option

← PrevNext →

Let us assume, I have a list of values (numbers or alphabets) in one column and each value has 6 digits. I want to mask the first 3 digits with "*" (asterix) and show the last 3 digits only, in a separate column. See the image below.

show last 3 digit in Excel cell using flash fill

It saves time. I’ll tell you how I did this.

I am using a feature called Flash Fill in Excel. The first column has a list a value prefixed with two zeros. I’ll the mask the "first" three values, and show only the "last" three values in the next column.

What I did is, selected the first value (cell D4) and typed three "*" (or asterix symbox) followed the last three digits in the next column. Then I shifted focus to the next row of the 2nd column (D5) and pressed CTRL+E keys and Excel "automatically" filled the remaining cells with values by sensing a pattern.

Note: You can set focus anywhere between the D4 and D9 cells, type a pattern (any pattern) and press CTRL+E.

flash fill example in Excel

The shortcut key CTRL+E is for Flash Fill option. You can find this option inside Data Tools section in Excel.

In your Excel worksheet, select Data from the top menu and find Flash Fill option under Data Tools section (or group).

flash fill option tab in Excel

You should also know this

Remember, any changes you make to the values in the "first" column (the source), will not be be reflected in the "second" column (the result). You will have to apply flash fill again, manually.

This is not a formula. Maybe there is function written behind, which loops through each value in the first column and checks a pattern and write the result in the next column.

I guess, I can write a Macro to do the same. Just kidding. Why re-invent the wheel?

Conclusion

Its an amazing feature. It definitely saves lot of time. You can now mask hundreds of email ids, phone numbers etc. with the blink of an eye.

Thanks. 🙂

← PreviousNext →