- If you have a list
of unformatted phone values
like this one in Excel?
Creating hard-coded phone numbers
from it can be tricky.
And this is because
Excel's phone number formatting options,
they don't actually change the contents
in the cell.
They only make them look like phone numbers.
So for example, let's say we highlighted these.
We go to Format Cells.
Under the Special category,
select Phone Number.
This is going to format our values
as phone numbers.
But notice up here
in the Formula Bar,
we don't see that parentheses,
or this extra space, or the dashes.
This is because those characters
are only part of the formatting
of those cells.
They're not actually hard-coded
into the values in that cell.
Let's undo this.
The point is that we need to get creative
if we want to hard-code the phone values.
And I'll be honest,
I haven't actually found
a really great tool for this.
But there is one tool
that works most of the time
and this is Flash Fill.
So a quick recap.
Flash Fill works by detecting formatting patterns
as you type.
Once it detects a pattern,
it replicates it
in adjacent, consecutive cells,
hard-coding those results into the cells.
And if you want to learn more
about Flash Fill,
make sure you check out my tutorial on it.
So let's look at some examples.
Let's say we want
to get phone numbers with dashes
from these values in column A.
So in column B,
because we have to do this
in an adjacent column,
we would begin typing our entries
as we want them to look.
So in this case,
this is 123-456-7890.
Then we begin typing the next entry.
And you see, this grayed-out the rest
of that number
and then all the numbers below it.
Well, this is our Flash Fill.
And if we want to accept this fill,
if it looks right to us,
we would just press Enter
to fill the rest of those cells.
And what's great is,
let's say we have a new number
that gets added to our list
after we're all done.
Once we have that number,
we can press the Control + E keyboard shortcut
to fill in any new numbers
using the pattern from the values
just above it.
So you can also use Flash Fill
to create a list of phone values
that have multiple characters.
For example, let's say we want to do parentheses, 123,
close parentheses,
a space followed by 456, a dash, 7890.
a space, followed by 456, a dash, 7890.
And I'm gonna press Control + E
from my keyboard to fill in
the remainder of the phone numbers.
It's also even possible for you
to use Flash Fill
if your original list of values
is not consistent.
For example, here,
we have one number with dashes,
another one with period, slashes, et cetera.
So let's say we want
to do 123-456-7890.
This is where you have
to be careful to pay attention
to what the Flash Fill example
is offering you.
This fill is not what I want.
I actually want them all to have dashes.
I don't wanna just copy them as is.
So I'm gonna continue typing, add a dash.
And then do the same thing
to the third value.
And notice how now
my Flash Fill example looks correct.
And a quick FYI,
when the Flash Fill preview displays
can differ from user to user
or even between one attempt and another.
And this is because Excel
and Flash Fill are basically taking
into consideration other actions
that you've taken as you're working.
So it's trying to figure out
what you're gonna type.
So anyway, back to this example.
I'll press Enter to accept this.
So as I mentioned earlier,
Flash Fill works most of the time
to hard-code phone values,
but not all the time.
And that really depends
on how complex your phone formatting is.
An example of when I run into issues
is when I'm trying
to also add a +1
in front of the phone number.
And because that has to be done as text,
so Excel doesn't confuse the plus sign
as a calculation,
my guess is that it throws it off.
I don't know, I couldn't get this to work.
But let me show you what happens.
So let's say I do an apostrophe, a +1,
then space, parentheses, 123, parentheses again,
456-7890. If I press Control + E,
My Flash Fill is all wrong.
Right? Because I want to use plus one
for all of these at the beginning,
not the first number
that's listed in column A.
So let's undo this.
So if I keep typing +1 555,
close the parenthesis.
And say we'll type the third one,
+1 (246) 802-1357.
Notice how I'm still not getting
a Flash Fill example.
And we'll type one more to see
if I can get it to populate 123,
456-7890.
And I can't.
But let's say I want
to force the Flash Fill
and press Control + E. (bell dings)
What you notice is that it wasn't able
to find the pattern.
I just get an error message saying,
"We looked at the data.
"We couldn't see a pattern.
"Sorry, we can't help you."
So, this is an example of some
of the limitations of Flash Fill
to hard-code phone numbers.
But as you can see,
there is a lot that you can do with it.
And I'm curious to see
how you were able to use Flash Fill
to hard-code phone values.
So make sure to note in the comments
what results you got
when you try this method.
Thanks for watching.
For more tutorials like this,
make sure to click on these links.
Thanks!