format cell to accept 2 letters only

[Wanna Learn]

Hello column "F" is for states and I only want the abbriviation of each
state as follows 2 alpha charatects in upper case letters e.g. NY I
formatted the column as text but how do I do the data validation for two 2
characters and upper case letters or is there and easier way? VBA? thanks in
advance

 

[T. Valko]

You can use data validation.

Assume the cell of interest is F1.
Select cell F1
Goto the menu Data>Validation
Allow: Custom
Formula:

=AND(LEN(F1)=2,CODE(F1)>64,CODE(F1)<91,CODE(RIGHT(F1))>64,CODE(RIGHT(F1))<91)

OK out

However, that will allow entries like BB, XX, II.

Those are obviously not state abbreviations!

Another way is to setup a list of the allowable abbreviations in a range of
cells.

Assume you list them in the range X1:X50
Then, select cell F1
Goto the menu Data>Validation
Allow: List
Source: =$X$1:$X$50
Uncheck: In-cell drop down
OK out

 

[Chip Pearson]

You can use the Validation tool on the Data menu. Select the cell(s)
whose input you want to restrict, choose Validation from the Data
menu, change the "Allow" type to Custom and enter


=AND(ISNUMBER(LEFT(F1,1))=FALSE,ISNUMBER(RIGHT(F1,1))=FALSE,LEN(F1)=2,EXACT(F1,UPPER(F1)))

Change F1 to the cell you are validating or the first cell if you are
validating multiple cells.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Thomlinson]

Create a list of the acceptable values. You can then use Data -> Validation
-> List to ensure that the input is valid. The only snag is that you can not
validate that the input was upper case that way. I personally would avoid
validating for upper case as the users will find it annoying. I personally
hate validation that says "I know exactly what you did wrong but instead of
handling the problem I am going to make you go back and fix it". You could
use custom validation if you really want, but I would be inclined to just use
the Upper function to convert the value to upper case in an adjacent cell.

 

[Joel]

Data Validation won't allow you just capital letters. Why don't you use
Vlidation and select List. Then enter the 50 abbreviations for the states
and let the user select a valid state abbreviation instead of enter the data
with the keyboard.

 

[Chip Pearson]

Data Validation won't allow you just capital letters.

Sure it will. Use EXACT in the custom formula:

EXACT(A1,UPPER(A1))


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Shane Devenshire]

Hi,

Make an upper case list of all possible state abbreviations. Select the
cell(s) where you want the data validation and choose Data, Validation, and
pick Custom from the Allow list. Enter the formula
=OR(EXACT(F1,$G$1:$G$50))

If you don't want to enter the list in uppercase you can use

=OR(EXACT(E1,UPPER($G$1:$G$50)))

instead

Here the first cell to in column F that I want to validate is F1 and I have
put a list of Upper case codes in the range G1:G50.

If this helps, please click the Yes button

Cheers,
Shane DEvenshire