Changing from upper case to lower case

[Louise]

Hi all

I can't believe I can't remember how to do this but how do you change text
in Excel from upper case to sentence case? I have a workbook with approx. 15
worksheets and everything is in upper case and needs changing.

Thank you.
Louise

 

[Sandy Mann]

Depending on what you have in your sheets, (it will choke on Pivot tables
etc.) trysomething like:

Sub ChangeCase()
Application.ScreenUpdating = False
For Each sh In Worksheets
sh.Activate
For Each cell In Range("A1:M300")
'change A1:M300 to your range needs
cell.Value = Application.Proper(cell.Value)
Next cell
Next sh
Application.ScreenUpdating = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Put this in a general module and run it

Sub change_Case()
For x = 1 To Worksheets.Count
Worksheets(x).Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select
For Each c In Selection
c.Value = LCase(c.Value)
Next
Next
End Sub


Mike

 

[Bill Ridgeway]

Hi all

I can't believe I can't remember how to do this but how do you change text
in Excel from upper case to sentence case? I have a workbook with approx.
15
worksheets and everything is in upper case and needs changing.

Thank you.
Louise

Excel offers only -
=lower(a1)
=upper(a1)
=proper(a1) (which capitalises everything)

The only way I have found to change to 'sentence case' is to -
copy and paste into Word
use <Format><Change Case><Sentence Case>
copy and past back into Excel

This may be a bit tedious and there may be other ways but it works!

Bill Ridgeway
Computer Solutions

 

[Louise]

Hi Mike

Thanks very much for this, it works, however, is there any way it can
capitalise the first letter of every word, as the worksheets contain people's
names and divisions.

Thanks again.

Louise

 

[Mike H]

Hi,

Change this
c.Value = LCase(c.Value)

to this

c.Value = WorksheetFunction.Proper(c.Value)

Mike

 

[Mike H]

Sandy,

Be careful with that one, it will change you formula to values.

Mike

 

[Louise]

That seems to have worked a treat and has saved me loads of time!! I still
struggle to get my head around VBA and can't actually write it myself.

Thanks very much.
Louise

 

[Louise]

Hi BIll

Thanks for your reply, however, I have copied the macro Mike created and it
works perfectly and takes a second to run!! If you do this quite often, it
might be worth copying the macro too.

Louise

 

[Mike H]

Louise,

Your welcome and thanks for the feedback

Mike

 

[Sandy Mann]

Thanks for the catch Mike - I didn't have any formulas in the W/B I tested
it on but the *Cell.Value* should have alerted me to the danger.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

Louise.

Just be careful that there are no formulas in your range to change.

They will be converted to values only.

Preferable to use c.Formula = WorksheetFunction.Proper(c.Formula)


Gord Dibben MS Excel MVP

 

[Mike H]

Gord,

the code I suplied won't change formula to values.

Mike