Excel Tips : Change case in cells (e.g caclubindia to CACLUBINDIA)
Using formula in excel cell, one can easily change the text case of data without having
to go in and rewrite each cell manually.
The formula gives, the option of changing the text case to
all lower case letters,
all UPPER CASE letters or
Proper Case letters (in which the first letter of each words is in upper case).
There are some ways to change the text case in Excel, one of this is using formula mentioned above.
Type in =Lower (cell Address) to change the text to lower case.
Type in =Upper(cell Address) to change the text to upper case.
Type in =Proper(cell Address) to change the text to proper case.
Example: In cell B2 type in: =Proper(A1) to change the text in the Cell A1 (A column & 1st Row)
to proper case. Now select cell B1, Press Ctrl-C, Select Cell A1, open Paste Special
& choose Paste Values Or use [use shortcut key (in Excel 2003: Alt-E,V,S Press Ok),
(in Excel 2007+: Press Alt,H,V,V)]
Afterwards you may remove formula in B1.
Another method is using Microsoft words.
Select the text in the cells, Press Ctrl-C to Copy & switch to Microsoft Words new document, press Ctrl-V to paste there.
Here you can easily change text case from menu\ribbon or just by pressing Shift-F6. After changing desired case select this text, press again ctrl-c to copy & switch over to Excel's desired location & Press Ctrl-V to paste.
If all this still considered lengthy, you may use one excel add-ons specifically written for this features (Microsoft didn't provide still this in Excel-2010!) which easily & quickly change case of text from cells. With this you can also change case to sentence case (i.e. first letter of sentence is in Upper case)
Download zip file & Unzip it, Run setup.exe to install this add-ons for Excel.
If you don't need this add-ons afterwards, you may uninstall from Control Panel, Add Remove Program Features
To open dialog box (see below image), click on menu>Format>Change Cell Case in Excel-2003 or in Excel-2007, click on Addins Tab.
1. With this you can change case of selected text in textbox.
2. switch to\open another worksheets while the dialog box is open.
3. Names like mr mcritchie will be Mr McRitchie for Sentence & Proper Case.
4. i & i'm will be I & I'm for sentence case.
(MSWord's Format>ChangeCase>sentence case doesn't do this changes.)
Enjoy excelling!
CAclubindia
