convert text to sentence case
 

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

--
Jacqueline

convert text to sentence case
 

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

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

--
Jacqueline

convert text to sentence case
 

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

"Mike H" wrote:

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

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

--
Jacqueline

convert text to sentence case
 

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

On Tue, 20 Jan 2009 12:54:05 -0800, Jacqueline
wrote:

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