top of page

You are learning Data Validation in MS Excel

How to create input masks for data validation in Excel (e.g., phone number format)?

Unfortunately, Excel doesn't directly support input masks for data validation like some other programs. However, you can achieve a similar effect using a combination of data validation with a custom formula. Here's how to create data validation with a phone number format:

1. Select the cell(s): Choose the cell(s) where you want to restrict data entry to a phone number format.
2. Data Validation: Go to the "Data" tab and click "Data Validation" in the "Data Tools" group.
3. Settings Tab: In the Data Validation window, ensure you're on the "Settings" tab.
4. Allow: Custom: Under "Allow," choose "Custom" from the dropdown menu.
5. Formula for Phone Number Format: In the "Formula" box, enter the following formula:

```excel
=LEN(TEXT(A1,"(000) 000-0000"))=14
```

Explanation of the Formula:

- `A1` represents the cell you're applying the validation to. Adjust this cell reference if needed for your specific range.
- `TEXT(A1,"(000) 000-0000")`: This part of the formula attempts to format the value entered in cell A1 as a phone number with the specified mask (カッコ - kakko - parentheses for area code, spaces for separation).
- `LEN()`: This function counts the number of characters in the formatted text.
- `=14`: This checks if the length of the formatted text (including parentheses and spaces) is equal to 14, which is the expected length for a phone number with this format.

What the Formula Does:

This formula essentially checks if the entered data can be formatted as a phone number with the specified mask. If it can be formatted correctly and has the expected length of 14 characters, the data is considered valid. Otherwise, it will trigger a data validation error message.

Customizing the Phone Number Format:

You can modify the mask within the `TEXT` function to match your desired phone number format. For example, for a format without parentheses:

```excel
=LEN(TEXT(A1,"000-000-0000"))=12
```

Data Validation Error Message (Optional):

You can set a custom error message to be displayed if the data entry doesn't meet the criteria. In the Data Validation window, go to the "Input Message" tab and type your message (e.g., "Please enter a valid phone number using the format (000) 000-0000").

Limitations:

- This method doesn't actually force the data to be entered in the specific format. It only validates if it can be formatted correctly afterwards.
- You'll need to create separate formulas for different phone number formats.

While not a perfect replacement for an input mask, this approach using data validation with a custom formula can help ensure users enter phone numbers in a consistent format within your Excel sheet.

bottom of page