How can I force certain text formatting in a cell?

[Guest]

I'm building an invoice template for my own use (self employed trade). I
want a cell to be a properly formated 2-letter state abbreviation in caps no
matter how I enter it. I suppose that will involving limiting the length ot
the text to 2 letters and performing some action to capitalize the text if
necessary. I don't know if the best way to do this is with a macro or some
other way. I think I would prefer a macro to automatically capitalize all
letters in the cell after the text is entered no matter how exited (enter,
tab, mouse click).
I'll settle for the capitalization if the 2-letter limit is too hard.
I know I could do a data validation list, but I'd rather not.

Thanks

 

[Oliver Ferns via OfficeKB.com]

Why not use validation??? That's what it is there for! There are other ways
but you don't say which 2 letters you want to keep so I will assume it is
the first 2 typed and cell is A1...also, what happens if only 1 character
is entered? Do you want an extra character added? anyways...here goes...

Put this code behind the relevant sheet.....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Target.Value = UCase(Left(Target.Value, 2))
End Sub


This will trim the contents of A1 to 2 characters and ensure they are
capitals...

Hth,
Oli

 

[Earl Kiosterud]

eddied,

Data Validation can ensure that you use only two characters, and also that
only valid state abbreviations are used. It seems quite perfect for your
requirement. the following formula, used in Data - Validation - Custom,
will ensure that only entries in your list are used, and that they've been
entered as uppercase:

=AND(OR(CODE(A1)=CODE($B$1:$B$50)))

The list of valid abbreviations is in B1:B50. Change the formula as needed.
Change the A1 to the active (white) cell of your selection for validation.

If you want it to change a lowercase entry to upper automatically, it will
require a macro. You could possibly use AutoCorrect but might have
interactions with ordinary text, and it would apply to other Office programs
(Word, etc.)