Check for text, numbers & length
Excel Custom Data Validation
Custom data validation is especially useful when the normal data validation criteria options are insufficient, this way, formulas can be used to define the restrictions.
TIP: Since the formula text box in the Data Validation window will not provide formula tips as you write them, an easier way is to write the formula on the spreadsheet itself and then transfer it to the data validation window.
Case 1: Company code is composed of 5 characters. First character is any text, followed by 4 digits
Setting up formulas
- Length is 5 characters
1. The LEN() function is used to count the number of characters in a specified cell.
Its syntax is: LEN(text).
This now becomes Cell B5 =LEN(A5).
2. Set up the formula so that it shows whether an entry is valid or not.
To do this, equate the formula to the desired value.
It now becomes Cell B5 =LEN(A5)=5.
This will return a TRUE or FALSE result based on the contents of the cell.
- Last 4 characters are numbers
This means that the combination of these numbers must be a number
1. The RIGHT() function allows you to isolate a specified number of characters to the right of the cell contents.
Its syntax is: RIGHT(text, [num_chars]).
To display the last 4 characters of cell A5:
Cell C5 = RIGHT(A5,4)
2. Add the function ISNUMBER() to indicate where the isolated text is a number and will return a TRUE or FALSE based on the content.
The formula now becomes:
Cell C5 = ISNUMBER(RIGHT(A5,4))
3. However, the result of the RIGHT() function is a text and so the ISNUMBER() function marks this as FALSE regardless of the content.
To address this, the VALUE() function is used to wrap around the RIGHT() function.
This now becomes:
Cell C5 = ISNUMBER(VALUE(RIGHT(A5,4)))
- First character is non-numeric
1. The LEFT() function isolates a specific number of characters on the left side of a cell contents.
Its syntax is: LEFT(text, [num_chars]).
To isolate the first character of cell A5:
Cell D5 = LEFT(A5,1)
2. Instead of using the ISTEXT() function and figuring a workaround to convert the output of the RESULT() function to the correct type, the ISNUMBER() function can be used.
However, instead of keeping it similar to the previous requirement, it should do the opposite and return TRUE when it is a text and FALSE when it is a number.
To do this, introduce the function NOT(). This now becomes:
Cell D5 = NOT(ISNUMBER(VALUE(LEFT(A5,1))))
Merging the formulas
All these requirements should be satisfied in order to pass the data validation, otherwise, it should be regarded as an invalid entry.
To do this, an AND() function is used.
- Activate the Clipboard for convenience. This is found under Home > Clipboard.
This can also be done by pressing CTRL + C + C when it is enabled.
2. Highlight the formula to be copied one by one and press CTRL + C.
This transfers it automatically to the clipboard.
3. In a separate cell, merge the three formulas using the AND() function and selecting the formulas from the clipboard section to insert it.
It now becomes:
=AND(LEN(A5) = 5, ISNUMBER(VALUE(RIGHT(A5,4))), NOT(ISNUMBER(VALUE(LEFT(A5,1)))))
Setting up custom data validation
- Copy the final formula to be used
- Select the cell for data validation, cell A5
- Go to Data > Data Validation
4. Under Data Validation, select Allow: Custom. Paste the formula in the textbox.
5. If needed, the data validation setup can be copied to other cells by pressing CTRL + C while selecting the cell with the data validation. Highlight the other cells then right click > Paste Special > Data Validation.
6. To add an input message, go back to Data Validation > Input message tab and specify the message to be displayed.
7. To customize the error alert, go to Data Validation > Error Alert tab and fill in the details.
Case 2: Project code starts with the letters “PT” followed by four digits
Setting up formulas
Case 3: Company code starts with two characters that are non-numeric followed by four digits
Setting up formulas
- Length = 6
Cell B17 = LEN(A17)= 6
- Last 4 characters are numeric
Cell C17 = ISNUMBER(VALUE(RIGHT(A17,4)))
- First two characters are non-numeric
The third formula used in Case 1 is not applicable to test both first two characters right away because this will result to a false positive when one of the two is not a number.
Cell D17 = NOT(ISNUMBER(VALUE(LEFT(A17,2))))
This formula should be changed to test the first character and second character separately.
- Test the first character in a way similar to Case 1:
Cell D17 = NOT(ISNUMBER(VALUE(LEFT(A17,1))))
2. Isolate the second character using the MID() function.
This enables you to take any character from a cell by specifying values.
Its syntax is: MID(text, start_num, num_chars).
The formula below takes one character starting from the second character of cell A17:
Cell E17 = MID(A17,2,1)
3. Make sure that the second character is not a number by using a combination of NOT(), ISNUMBER() and VALUE() functions:
Cell E17 = NOT(ISNUMBER(VALUE(MID(A17,2,1))))
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW