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

Use =LOWER(A1) to change to lower case or =PROPER(A1) to make the leading
letter capital

edvwvw

Louise wrote:
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

--
Message posted via http://www.officekb.com

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


Replace @mailinator.com with @tiscali.co.uk


"Louise" wrote in message
...
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,

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

Mike

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Louise" wrote in message
...
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

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


Replace @mailinator.com with @tiscali.co.uk


"Mike H" wrote in message
...
Sandy,

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

Mike

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Louise" wrote in message
...
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

"Louise" wrote in message
...
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

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

"Bill Ridgeway" wrote:

"Louise" wrote in message
...
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

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

"Louise" wrote:

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

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" wrote:

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

"Louise" wrote:

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

Hi,

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

to this

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

Mike
"Louise" wrote:

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" wrote:

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

"Louise" wrote:

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

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

"Mike H" wrote:

Hi,

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

to this

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

Mike
"Louise" wrote:

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" wrote:

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

"Louise" wrote:

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

Louise,

Your welcome and thanks for the feedback

Mike

"Louise" wrote:

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

"Mike H" wrote:

Hi,

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

to this

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

Mike
"Louise" wrote:

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" wrote:

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

"Louise" wrote:

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

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


On Tue, 27 May 2008 04:21:00 -0700, Louise
wrote:

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

"Mike H" wrote:

Hi,

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

to this

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

Mike
"Louise" wrote:

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" wrote:

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

"Louise" wrote:

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