Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
eddied
 
Posts: n/a
Default How can I force certain text formatting in a cell?

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
  #2   Report Post  
Oliver Ferns via OfficeKB.com
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com
  #3   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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.)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"eddied" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 08:10 PM
Moving text from one cell to another. estaban botas Excel Worksheet Functions 1 December 15th 04 03:04 PM
how to hyperlink text to a cell steve New Users to Excel 1 December 14th 04 09:21 PM
Formatting a cell as "text" in the number catagory. Ed Excel Worksheet Functions 3 December 7th 04 08:12 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"