Formatting cells as Title Case, etc.

CompTutor

New Member
Joined
May 7, 2002
Messages
23
I teach newbies :eek: , and we need to know if anyone has a way to format cells SO THAT WHEN THE TEXT IS TYPED in lowercase, for instance, it changes to Title Case, or UPPERCASE, or whatever, IN THAT CELL. (The Proper Fx only works in another cell, if you see what I mean.)

Caveat: we need a solution that can be used by newbies, without macros or VB, and without add-ins, because schools don't allow that. While I suspect there wouldn't be add-ins if this were easy, I have to ask.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
You gotta do it with a macro:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)

    Target.Value = UCase(Target.Value)

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You can use a formula:

=UPPER(A1)

But as you pointed out, it will only provide the data in another cell. Basically, you can use macros or you can use a formula. Other that that, you have no other options.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...without macros or VB..."

can't be done, in that case.

Options are:

1) given a data entry field, use a formula to return the appropriately formatted result in another cell;
2) use vba to check / alter the data in the same cell ('cos you can't have a formula & data entry in the same cell)
3) use data validation to check if the entry is formatted OK & get user to re-enter if not. the validation rule would look something like:

=EXACT(A1,PROPER(A1))
 

CompTutor

New Member
Joined
May 7, 2002
Messages
23
Your answer(s) not only helped, but they arrived BEFORE class was over, thereby demontrating, once again, the value of ME.C to us poor Excel users--AND to a whole class. That is another 18 people who will not forget you.

This site never ceases to amaze me--it is one of the few places I know of that I can lurk and ALWAYS learn something new.

THANK YOU!!! :pray:

(Now if only we could get MS to add this little trick to the format cells dialogue box, like they did to tables in Word '03...)
 

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943

ADVERTISEMENT

As an afterthought, why not use a "Titling" font for the cells, or for the entire sheet for that matter? Just select all,then set the font to Perpetua Titling, or Felix Titling. These fonts have just a single case... UPPER, no matter whatcha type! Hope this simpler solution can help! /s/ Larry
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Now if only we could get MS to add this little trick to the format cells dialogue box, like they did to tables in Word '03
Actually, there is an Add-in, authored by Ivan Moala, MVP this Board, that does just that. You may be interested in the add-in for personal use.

The add-in will put a new entry in your menu set-up. Simply select the text that you wish to format, access the menu item and select either Uppercase, Lowercase, Sentence, or Proper case (just like MS Word), with the added bonus of a SmallCaps case. Re-formatting is done "in cell".

The add-in is called “Text Case changer”
http://www.xcelfiles.com/Downloads_01.html

Regards,

Mike
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,566
Messages
5,765,150
Members
425,264
Latest member
Towervibe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top