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
=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
=LEN(A5)=5This 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:
= 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:
= 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:
= 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:
= 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:
= 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
- Length = 6
= LEN(A11)= 6 
- Last 4 characters are numeric
= ISNUMBER(VALUE(RIGHT(A11,4))) - First two characters are “PT”
Isolate the first two characters and then equate it to “PT”.
The LEFT() function can be used here.
This now becomes:
= LEFT(A11,2) =”PT” Merging the formulas
Similar to Case 1, the AND() function will be used to make sure all three conditions are met.
The final formula now becomes:
= AND(LEN(A11)= 6, ISNUMBER(VALUE(RIGHT(A11,4))), LEFT(A11,2) =”PT”) 
Case 3: Company code starts with two characters that are non-numeric followed by four digits
Setting up formulas
- Length = 6
= LEN(A17)= 6 - Last 4 characters are numeric
= 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.
= 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:
= 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:
= 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:
= NOT(ISNUMBER(VALUE(MID(A17,2,1)))) 
Merging the formulas
Again, the AND() function is used and the final formula is:
= AND(LEN(A17)= 6,
ISNUMBER(VALUE(RIGHT(A17,4))),
NOT(ISNUMBER(VALUE(LEFT(A17,1)))),
NOT(ISNUMBER(VALUE(MID(A17,2,1))))) 
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
