We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
Trying to hard-code phone numbers in Excel from a list of phone values that were not correctly formatted can be tricky (if you don’t want to do it manually!).
This is because Excel’s phone number formatting options don’t actually change the contents in the cell, they only make them look like phone numbers.
But what if you need the dashes, parentheses, and whatever other characters you use in your phone formats to be hard-coded into your sheet???
To be honest, I haven’t found a perfect solution to this.
So, in this tutorial, I’m going to share an option that works for me (most of the time!): Flash Fill
NOTE: Flash Fill is available in Excel 2013 and newer.
Use Flash Fill to Hard-Code Phone Values into Excel
Flash Fill works by detecting formatting patterns as you type. Once it detects a pattern, it replicates it in adjacent, consecutive cells.
The Flash Fill results are then all hardcoded into these cells.
NOTE: By “consecutive cells,” I mean that, if there is a blank cell in your list of values, Flash Fill will stop the fill once it comes to the blank cell. It assumes the blank cell is the end of the list.
If you like, you can then copy and paste the new phone values from your Flash Fill into a new file or paste over the original list of values.
Learn About Flash Fill: 3 Ways to Use Flash Fill in Excel with Examples (+ video tutorial)
Limitations of using Flash Fill to hardcode phone values in Excel
No tool is perfect. While Flash Fill works most of the time, there will be times that it’s unable to identify a pattern.
When this happens, you won’t be able to use it for the purpose of hard-coding phone values.
Also, the number of entries before the Flash Fill preview displays can differ from user to user or even between one attempt and another. This is because it also takes into consideration other actions taken.
Let’s look at some examples of how you can use Flash Fill to hardcode phone numbers in Excel.
Ways to Use Flash Fill to Hard-Code Phone Values in Excel
WATCH: How to Hard Code Phone Numbers in Excel by Using Flash Fill – Video Tutorial
Use Flash Fill to Hard-Code Dashes into a List of Phone Numbers (or periods, and spaces too!)
Once Flash Fill has identified a pattern, it will fill the cells next to your list using this pattern.
For example, let’s say you have a list of values in column A and need to generate a list of those values be phone numbers with dashes:
The values in Column A may look like: 1234567890
You need them to be: 123-456-7890
In column B (you need to do this in an adjacent column), you would type the first entry as you want it to look “123-456-7890.”
Once you begin typing the next entry, the Flash Fill preview will display.
If you’re happy with it, press Enter to accept the fill and populate the phone numbers with dashes for you!
FYI: You can also press Ctrl+E from your keyboard after typing the first entry to force the Flash Fill.
TIP: You can tell that the Flash Fill values are hard-coded by looking in the Formula Bar. If the dashes appear in the formula bar, then they are hard-coded in the cell. If the dashes were merely formatted (and not entered in the cell), you would not be able to see them in the Formula Bar.
You can also use Flash Fill to hard-code phone numbers with periods and spaces too!
Hard-Code Phone Numbers with Multiple Formatting Characters
You can also use Flash Fill to create a list of values with phone formats that include multiple characters.
For example, a phone format with parentheses, spaces, and dashes:
The values in Column A may look like: 1234567890
You need them to include parentheses, spaces, and dashes: (123) 456-7890
NOTE: When the Flash Fill preview displays can differ from user to user or even between one attempt and another. This is because it also takes into consideration other actions taken.
Use Flash Fill to Create a List of Hard-Coded Phone Numbers from a Non-Consistent List of Values
You can also use Flash Fill when the list of values is not consistent.
Let’s say column A has phone values where some have periods, some have dashes, and others have slashes. In other words, they are all formatted differently.
In this situation, you can still give Flash Fill a try to create a list of values that have hard-coded, consistent phone formats.
As always, make sure to check that the fill you’re accepting is how you want it!
When I worked through this example, the first Flash Fill option that populated was not correct; the first 3 digits were in parentheses, but the remainder all used different characters.
To try to force the correct Flash Fill, I ignored this and continued typing.
With my next entry, the correct formatting finally appeared!
When Flash Fill Doesn’t Work to Hard-Code Phone Values
As I noted in the intro to this tutorial, Flash Fill works most of the time to hard-code phone values, but not all the time.
For example, I was unsuccessful when I tried to format the phone values to include a +1, parentheses, and a dash.
The Flash Fill that populated was completely wrong. Instead of adding a +1 at the beginning, it attempted to use the first number in the cell.
Not what I wanted.
I then manually typed a few more examples and nothing.
I even tried forcing the Flash Fill using the Ctrl+E keyboard shortcut. Doing this gave me an error message that a pattern was not able to be detected.
While using Flash Fill to hard-code phone numbers in Excel definitely has its limitations, it does work for most uses. All in all, making it a pretty handy tool for this purpose.
I would love to know how you were able to use Flash Fill to hard-code phone values! Make sure to note in the comments what results you got when you tried this method.
Related: How to Format Phone Numbers in Excel (+ video tutorial)
Next: How to Add Zeros in Front (Leading Zeros) in Excel (+ video tutorial)
FYI, this tutorial was created using the desktop version of Excel in Microsoft 365.
Leave a Reply