Particular Format


Highlight the cells

This example shows you how to restrict the data entered to
There may come a time when you want to restrict the exact format of a text string.
In this example we want the string to be exactly 5 characters long and to start with a letter.


Enter the Conditions

Press (Data Validation) to display the Data Validation dialog box and select the Settings tab.
Select Custom from the Allow drop-down box.
Enter the following formula into the formula box.
=AND(LEN(C6)=5,CODE(LEFT(UPPER(C6),1))>64,CODE(LEFT(UPPER(C6),1))<91)
The AND function returns the logical AND for any number of arguments.
The LEN function returns the number of characters in a text string.
The CODE function returns the ANSI number for the first character in a text string.
The LEFT function returns the first or left most characters in a text string.
The UPPER function returns the text string with all the characters converted to upper case.

Press OK to apply the data validation.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext