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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
"...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))
 
Upvote 0
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...)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top