Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

Hi Cecile,

Try:

Sub Tester()
Dim rng As Range
Dim rCell As Range

Set rng = ActiveSheet.UsedRange

For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)
With rCell
.Value = Application.Proper(.Value)
End With
Next
End Sub

If you are unfamiliar with macros, see David McRitchie's Getting Started
with Macros and User Defined Functions notes at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman



"Cecile" wrote in message
...
Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

If you mean that you want only the 1st letter of the 1st word capitalized, you
can use this formula in another column:

=UPPER(LEFT(A1,1))&LOWER(MID(A1,2,255))

Copy down as far as needed, then Edit/Copy the column with the formulas and
Edit/Paste Special and select the Values option to replace the formulas with
the literal text. Then you can delete the column with the original text.

On Sun, 12 Dec 2004 18:04:01 -0800, "Cecile"
wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

Cecile

Would you be interested in using a macro?

Either for Sentence Case or Proper Case....your choice or both.

This is sentence case. First letter upper after each period. Like this.

This Is Proper Case. All Words Upper First Letter.


Gord Dibben Excel MVP

On Sun, 12 Dec 2004 18:04:01 -0800, "Cecile"
wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

Hi all, thanks for your replies, I will definitely give them a try tomorrow.

Yes Gord I'd be interested in using a macro, sentence case is what I'm after.

Cheers,

Cecile.

"Cecile" wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

Cecile

A UDF and a macro that references it.............

Function CapFirst(ByVal str As String) As String
Dim aRegExp As Object, aMatch As Object, allMatches As Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(str)
For Each aMatch In allMatches
With aMatch
Mid(str, .firstindex + 1 + .length - 1, 1) = _
UCase(Mid(str, .firstindex + 1 + .length - 1, 1))
End With
Next aMatch
CapFirst = str
End Function

Sub Sentence_Case()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """" & ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub


Gord

On Tue, 14 Dec 2004 02:53:02 -0800, "Southcecile"
wrote:

Hi all, thanks for your replies, I will definitely give them a try tomorrow.

Yes Gord I'd be interested in using a macro, sentence case is what I'm after.

Cheers,

Cecile.

"Cecile" wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a lot
of upper cases. How can I change the text to sentence case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.

Thank you Gord,

I'll give it a go!

Cheers,

Cecile.

-----Original Message-----
Cecile

A UDF and a macro that references it.............

Function CapFirst(ByVal str As String) As String
Dim aRegExp As Object, aMatch As Object, allMatches As
Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(str)
For Each aMatch In allMatches
With aMatch
Mid(str, .firstindex + 1 + .length - 1, 1) = _
UCase(Mid(str, .firstindex + 1 + .length - 1,
1))
End With
Next aMatch
CapFirst = str
End Function

Sub Sentence_Case()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """"
& ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub


Gord

On Tue, 14 Dec 2004 02:53:02 -0800, "Southcecile"
wrote:

Hi all, thanks for your replies, I will definitely give
them a try tomorrow.

Yes Gord I'd be interested in using a macro, sentence
case is what I'm after.

Cheers,

Cecile.

"Cecile" wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a
lot
of upper cases. How can I change the text to sentence
case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.


.