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:
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
Select the cells and Click 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.
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.
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.
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.
Click Ok button to save the Data Validation settings. Now let’s check the validations.
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.
And if we enter a valid format, Excel accepts and will not display alert messages.
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|