Home / Validation / How to Force Users to Enter a Custom Format Using Data Validation

How to Force Users to Enter a Custom Format Using Data Validation

The Excel HelpDesk Support team received the following request for help:

Problem – Need user to enter a custom format

I need to do a column with eight characters and I can not allow nine or more. Please see the example. How can I do this? The characters are seven numbers and one dash (-).

Thank you, have a great day.

Microsoft Excel offers various approaches on providing specific formats for user input. We can use formula, data validation, format cells or VBA depending on the complexity of the format. For complex formats such as product ID and email address, we can use VBA and take advantage of the powerful regular expression. But since the requirement is for a formula in this case, using Data Validation feature is sufficient and as effective. Below is the screenshot of the example mentioned by the requestor:

Actual File

 

The requirements for the input are listed below:

  • Exactly eight characters
  • First two characters are numbers
  • Third character is a dash
  • Last five characters are numbers
This can be achieved easily if you right click the cell and choose Format Cells, select Custom in the Category list then enter 00-00000 in the Type input box. Excel will try to format the user input automatically. It would work for some data and most likely with numbers. If input is 3245324, it will be formatted to 32-45324 automatically, but if the input is a text like “try me!”, it will be ignored and no formatting will occur.

 

To enforce the user to enter a valid custom format, we will use Data Validation since it provides the same functionality but will not allow invalid inputs by prompting users with an alert message. I would suggest you to read a related post here entitled How to Use Data Validation since it discusses the main functions and has broader explanation of its capabilities. Here are the steps on how to implement the Data Validation for this scenario.

 

Select the cells and Click Data Validation

 

select the cells for data validation

 

Select the cells or column first where the user is expected to input the data then go to Data > Data Validation.  The Data Validation window will appear. In the Allow dropdown, select Custom. Also ensure that the Ignore blank checkbox is checked.

 

data validation window

 

In the Formula field, enter the following:

=AND( ISNUMBER(–LEFT(A12,2)),MID(A12,3,1) = “-“,ISNUMBER(–RIGHT(A12,5)),LEN(A12) = 8 )

 

As you can see in the formula, we are using a number of built-in Excel functions. We will try to explain each  briefly so we know what Excel is doing. Also note that the formula only refers to A12 cell. Make sure that when we finish saving all the data validation settings, all the references to cells will be adjusted accordingly.

  • AND(logical1, logical2, …) This function checks if all conditions inside are true.
  • ISNUMBER(value) Returns true if the value is a number. Note that if value is a number but data type is text, it will return false. (e.g. “2”). This is why we added a double negative sign (–) before the arguments. The double negative changes the sign of the value twice, which returns a number. Alternatively, we can also convert the value to number by adding zero or multiplying by 1.
  • LEFT(text, [num_chars]) Returns the specified number of characters from the start of the text.
  • RIGHT(text, [num_chars]) Returns the specified number of characters from the end of the text.
  • MID(text, start_num, num_chars) Returns the characters from the middle of the string, given a starting position and length. All arguments are required.
  • LEN(text) Gives the number of characters of the text.
The formula basically checks if the first two characters are numbers, third character is a dash, last five characters are numbers, and the whole input has 8 characters.

 

Modify Input Message and Error Alert

 

One advantage of Data Validation is that it provides customization of message and alerts. The Input Message is displayed to the user whenever the cells with Data Validation is selected. This is great since the user will have an idea what format is needed beforehand. To modify the Input Message, go to Input Message tab and enter the Title and Input Message just like in the image.

 

input message

 

Error Alert is shown whenever the user enters an invalid format in the cell with Data Validation. Select the Error Alert tab and modify the fields that suit your needs. The Style dropdown allows the user to select an appropriate style which changes the icon that appears in the alert window while the Title and Error Message fields are self-explanatory.

error alert

 

Click Ok button to save the Data Validation settings. Now let’s check the validations.

 

input message display

 

The input message is displayed when the cell is selected. If we try to enter an invalid format, just like in the image below, it will display the error alert window that we previously set up.

 

error alert display

 

And if we enter a valid format, Excel accepts and will not display alert messages.

 

valid format

 

I have attached a sample workbook for this post. If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

 

Download Sample Workbook Here

About Excel Help Desk