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))))