Here's something that you could potentially have to deal with in a Numbers spreadsheet.
Notice I've got this simple list here of names and phone numbers.
But the phone numbers are formatted in a whole bunch of different ways.
This can be very frustrating.
You can see here I've got ones with parentheses, with dashes, with dots.
I've ones that actually have no other marks except numbers leading Numbers to think that
it's an actual number and not text.
So how do you get all these to be one format.
Unfortunately there's no easy way to do it.
If you've ever dealt with this before and you've been frustrated there's a good reason.
There's no special formula function or formatting thing in Numbers that will take care of this.
So I want to give you two ways to deal with this.
So the first method is going to do the bulk of the work.
Not using formulas but just some simple searches.
I'm going to copy this entire set of phone numbers here, Command C, Command N to open
up a new Numbers document and paste it in here.
This is just going to be a temporary document so I'm not worried about formatting or things
like that.
I'm going to do Command F to find and I'm going to switch to Find and Replace and I'm
going to search for the characters I don't want.
So starting with the left parenthesis I'm going to hit Replace All and I'm going to
replace it with nothing.
Then I'm going to do right, Replace All, I'm going to do Space, Replace All, a dash and
Replace All and a period, Replace All.
You can see now I've cleaned up these numbers completely.
The reason I didn't do it here in the main spreadsheet was because I didn't want to,
say, replace the spaces in the names.
It would replace them throughout the entire spreadsheet.
So now that I've cleaned these up a bit I'm going to format these so they look like phone
numbers that I want.
So I'm going to go over to Cell here and I'm set the Format to be a Custom format.
This is where it gets tricky.
What I want to do is I want to use these integer formats here.
I'm going to set it to be a specific number of digits.
So three digits and then I'm going to surround that with parentheses and then a space.
Them I'm going to drag another one of these integers here and I'm going to set that to
also three digits, a dash, another integer set.
Leave this as four digits but I'm going to hide the separator.
So you can see now that looks like a phone number.
Sure enough, now I get a nicely formatted set of phone numbers there.
Now I can go and Copy, go back to this table here and go Edit and then I want to Paste
and Match Style.
Now I've got my phone numbers in there all nice and formatted.
So what if you wanted to use a formula for this.
It's going to be more work but it has the advantage of the fact that when you add new
phone numbers you can apply the same formula to it.
So let's Undo and get back to our mess here.
I'm going to use this next column to take a formula that's going to take this number
and format it correctly.
I've got this formula all prepared here and I've divided it into lines.
It's going to use the Substitute function to substitute characters for nothing.
So here is the center of it.
If I just did this part of it I have substitute taken from B2, a space, with nothing.
Then what I'm going to do is I'm going to wrap that, take the result from that, and
wrap that into a substitute that looks for a dash and replaces it with nothing.
Then wrap that in a substitute that looks for a period and replaces it with nothing.
Then a left parenthesis which is this here and a right parenthesis which is this here.
So when I copy this formula here, cause it is a lot easier to make in TextEdit than it
is here, I can paste it in and you can see it's going to do some formatting.
Like this occurrence here is I can set the number of occurrences.
I'm going to leave that blank because I want the default.
I'm going to get what I want though.
It's going to take out those five different characters.
If I Copy and Paste it in it's going to do it for all of them.
So you can see here that it's, you know, removing whatever characters are needed.
It's even working with the ones that are formatted as numbers rather than text because it makes
the assumption when I started to use substitute that I wanted to convert it to text.
Now in order to get this to work with formatting I'm going to have to do Value and put that
there and close the parenthesis at the end.
So now I'm going to get a number and I'll copy and paste that in.
So now I'm getting number results.
Now that I have number results I can go into Formatting, Create Format, and do what I was
doing before where I can have three digits, three digits, four digits which no separator
and put my formatting in place.
Left paren, right paren, space and dash.
See now it converts them all.
The great thing is if I add another number here, you can see it converts it there.
So it will do it each time because of the formatting and the formula I have in place
there.
If I want then I can temporarily hide this column when I need to do things with my spreadsheet
like print it out or things like that.