Last week, I talked about using Excel tools to validate a U.S. state in your addresses. Continuing with that thought, today we’ll look at validation of a phone number in Excel.
There are lots of database products out there that have some built-in validation for phone number. But if you’re like most small businesses, you keep at least some of your data in Excel. Excel is a great tool for this, but it lets you get sloppy, which causes headaches down the line. Let’s look at an example:
In this example, you can see that phone numbers have been entered in a variety of formats. Some use dashes, some use parenthesis, some have extensions. And some have none of the above.
If this is the case for you, you’ve got two things you need to do. First, you need to make sure that proper phone numbers are entered going forward. Second, you need to clean up your existing data. Today we’ll talk about the first: adding validation and formatting to make sure you’re getting good data entered into your worksheet. As far as cleanup of existing data, check out my prior post on cleaning your data using Microsoft Access tools.
Phone Number Validation
In order to control how phone numbers are entered going forward, you can use a combination of formatting and data validation. Let’s start by looking at formatting.
If you have good users, and you can count on them to adhere to your guidelines, your problem might be solved by adding formatting to your phone number column. In this scenario, you’ll ask users to type in only numerals. Your formatting rule will then add dashes or parenthesis, as appropriate. Let’s see how that works.
I’m going to select column C, which contains my phone numbers. I’m selecting the entire column, so that the formatting rule I create will apply to all rows as new vendors are added. My formatting rule looks like this:
I’ve chosen the “Custom” category on the left, and then typed in “###-###-####” under “Type”. This coding tells excel that we’ll have three digits, followed by a dash, followed by three more digits, followed by another dash, followed by four digits. Let’s try it.
In cell C9, I’ll type in a number – digits only – as you see here:
When I press Enter, Excel transforms that number, inserting the dashes in the appropriate spots:
Looks great, but there are some issues with this.
First, let’s see what happens when the user doesn’t adhere to our requirement of typing in numerals only:
In all of these cases, the user typed in extra characters in addition to the numerals. The last one might surprise you. In that case, the user typed in spaces between the numbers. From Excel’s point of view, those space characters were unexpected, and it doesn’t understand that it should replace spaces with dashes.
In all cases, Excel was not able to format the number correctly.
Max Characters
How do we correct this? One option is to add some data validation. In our case, we want the user to type in exactly 10 characters (9998887777). In all of the cases shown above, the user typed more than 10. Remember that spaces count, so even the last example was more than 10.
I’ll add a simple validation rule that restricts this column to exactly 10 characters:
Now I’ll try adding a phone number with an extension, parenthesis or spaces. An error message will be displayed:
Numbers Only
While this is a big improvement over where we started, we’re still allowing people to enter any 10 characters, as you see here:
This new entry passed our validation, because its exactly 10 characters. But obviously, it’s not valid. To correct this, I’d like to limit entries in this column to numbers only. I’ll change the Data Validation rule to use the “ISNUMBER” function, which, as the name implies, checks to see if a given cell contains a numeric value:
Note: Don’t be confused by the fact that I’ve got “C1” in that formula. That’s a relative reference. Look at column C behind the dialog box – see that cell C1 is active? That’s why C1 is in the formula. But when the rule is applied to any cell in column C, that reference will be relative to the row that’s active. So if you’re on cell C10, the formula will actually be “=ISNUMBER(C10)”. The formula automatically adjusts for each row.
Now when I enter a new value in this column, it throws an error if there are any non-numbers, as you see here:
This works for parenthesis and spaces, as well as extensions.
The problem now is that we’ve removed the 10-character limit. I can now enter only numbers, but as many of them as I like. Both of these are considered acceptable:
As you can see, the formatting rule adding the dashes, but we still don’t have valid phone numbers.
Putting it All Together
The answer is to put both validation rules together into a single formula. Remember that you can nest formulas. We’ll use the “And” function, and nest both the ISNUMBER and LEN functions inside of it:
This works great. I’m now limited to entering numbers only, and exactly 10 of them. Along with my formatting rule, this ensures that my phone numbers will now be consistent on all new entries.
Smoke and Mirrors
One thing to be aware of is that the dashes aren’t actually part of the content of the cells. Only the numbers are actually there. The dashes are simply cosmetics added by Excel. To prove this, I’ll add a simple formula to another cell, referencing one of the phone number cells:
As you can see, the actual value of the cell does not contain the dashes. In most cases, this is probably not a problem for you, as long as you keep your data in Excel. In fact, if you simply add a reference to the cell without the “VALUE” function, Excel is polite enough to copy over the formatting, thus making the number appear correctly.
I only mention this because if you’re going refer to this column in other worksheets or workbooks, or if you’re going to import this Excel data into some other software, the dashes may not be included. Sometimes they are, and sometimes they’re not. Within the Microsoft world, it seems that they are usually included, but keep an eye out for this.
How to Clean your Data
The rules we’ve set up will ensure that new values entered into your worksheet are correct. That doesn’t help with the mess you’ve already got, though. How do you clean those values?
If there are only a handful of errors, your best bet is to edit them manually. Now that the new rules are applied, you’ll be assured of getting proper phone numbers.
There are many ways you could correct these in Excel, which I won’t go into here. But in another blog post, I covered some steps you could take to clean this data up in Microsoft Access. View that blog here for details.
Next week, I’ll be looking at data validation and formatting for the Credit Limit column in my sample file. Of course, you can implement a lot of the steps we’ve taken here for that column, but we’ll dive in a little deeper, looking at some issues that’ll impact any math you might want to do on a numeric column. To get notified when that post goes live, join my mailing list using the form at the bottom of this page.
Have you used other techniques to validate phone numbers in Excel? Share your experiences in the comments below.
As always, I’m here to answer your questions. Call (978-582-9550) or email (kmartin@northportsoftware.com) to discuss your Excel or Access issues. I’m always happy to talk over your issue with you. If I can’t steer you in the right direction with just a short answer, we’ll schedule a one-on-one session to work together on it, or I’ll prepare an estimate for a longer project.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #smallbusiness #excel#microsoftexcel #msexcel #excelformulas
© North Port Solutions, LLC, 2022. All rights reserved.