Reformatting Phone Numbers in Numbers

If you have a list of phone numbers in a Numbers spreadsheet, you may find that they use a variety of different formats and you'd rather have them all in one format. You can do this one time using search and replace, then cell formatting to get the numbers to look the way you want. Or, you can use a long nested formula to remove all unwanted characters and then the same cell formatting.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Comments: 6 Responses to “Reformatting Phone Numbers in Numbers”

Christopher

7 years ago

Hello,

I enjoy your videos. This one makes me glad I store all my numbers in contacts!

Eric

Thank you for posting this.

Nello

here Numbers 3.6.2 El Capitain 10.11.6, pasting the formula from text edit, but the cell didn't formatting, any advice?

Thanks

Gary Rosenzweig

Nello: Not sure what you mean by "the cell didn’t formatting" -- what did you try, exactly, and what do the results look like, exactly? Did you enter it as a formula, or maybe paste it in as text by mistake? Make sure you type a = to enter the formula entry mode first, then paste in the formula.

Sorry i was putting one parenthesis in excess at the end of the formula, now everything works.

Yet another useful video from Gary.

Unfortunately, the example of entering a new phone number and having it automatically reformatted was off-screen in the video.

Comments Closed.

Reformatting Phone Numbers in Numbers

Comments: 6 Responses to “Reformatting Phone Numbers in Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information