convert text to sentence case

[Jacqueline]

Is there a way to convert all cap text to sentence case in Excel. I have to
dump data from one database into an Excel spreadsheet, where the data is
scrubed and then appended to a table in another database.

The data comes from the first database in all caps! Any help is greatly
appreciated.
Thanks

 

[Mike H]

Jacqueline,

There is no sentence case in Excel, there's Upper, Lower and Proper. The
latter is probably closest to what you want and you use

=Proper(a1) where a1 is the string but this capitalises the first letter of
every word.

If you only have a single sentence you could get away with

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

Which capitalises the first letter only, After that you need a macro so post
back if neither of these will do.

Mike

 

[Jacqueline]

I knew there had to be a way I just did not know what they were calling it.
Thanks Mike this is exactly what I needed.
Jacqueline

 

[Gord Dibben]

You sure you want "sentence case" and not Proper Case.

This is sentence case.

This Is Proper Case.

You can convert to Proper Case using the PROPER function in a helper column
or running a macro on the whole sheet.

Macro for Proper Case on selected cells...........................

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Macro for Senetnce case....................................

Sub Sentence_Case()
For Each Cell In ActiveSheet.UsedRange. _
SpecialCells(xlTextValues)
s = Cell.Value
Start = True
For I = 1 To Len(s)
ch = Mid(s, I, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, I, 1) = ch
Next
Cell.Value = s
Next
End Sub


Gord Dibben MS Excel MVP

 

[pedy]

Hi,

For the "Macro for Senetnce case", how would I make sure that "I" would always be in caps ?

Thanks

Pedy