One way is to try the sub SwitchCase* below,
which enables one to toggle the case of selected cells
*sub posted by Frank Isaacs '97
Steps
--------
1. Press Alt + F11 to go to VBE
2. Click Insert > Module
3. Copy > Paste the sub below
(everything between the dotted lines)
into the white empty space in the right-side window
-------begin vba------
Sub SwitchCase()
'Frank Isaacs May '97
'Select the cells and run
'Cells' case toggle order:
'if Lower > Upper
'if Upper > Proper
'if Proper** > Lower
'**or neither of the 3 case types
Dim Cell As Range
For Each Cell In Selection
Select Case True
Case Cell = LCase(Cell) 'It's lowercase
Cell = UCase(Cell)
Case Cell = UCase(Cell) 'It's uppercase
Cell = Application.Proper(Cell)
Case Else 'It's neither upper nor lower
Cell = LCase(Cell)
End Select
Next
End Sub
-------end vba------
4. Press Alt + Q to return to Excel
Running the macro
----------------------
5. Select the range of cells, e.g.: select A1:D200
(Or, to select entire sheet, press Ctrl + A.
The sub will work even on discontiguous selections/ranges.)
6. Press Alt + F8 (or click Tools > Macro > Macros)
In the dialog box:
Click on "SwitchCase" > Run
(or just double-click on "SwitchCase")
7. When complete, the text-case of the
selected cells will be converted
depending on their existing case, viz.:
Cells' case toggle order:
----------------------------------
if Lower > Upper
if Upper > Proper
if Proper* > Lower
*or neither of the 3 case types
To further toggle the case,
just run / re-run the sub over on the selected range(s)
---------------------
To make it easier to run / re-run the sub,
we could also assign the sub to a button drawn
from the Forms* toolbar in the sheet
(*If necesssary, activate toolbar via View > Toolbars > Forms)
Or, assign to an autoshape drawn in the sheet
(via right-click on autoshape > Assign Macro)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Philip" <[email protected]> wrote in message
news:[email protected]...
> Hello Friends
>
> There is an immediate need at my work place where I need to convert huge
> amount of data into different cases like proper, lower, upper and
sentence.
> If there is any freeware that lets me do all of the above please advice. I
> would also love to know how to write such programmes in VBA for Excel as
an
> interest(specially sentence case).
>
> Thank you
>
> Philip Jacob
> Senior Executive Quality Appraisal
> First American Corporation
Bookmarks