Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sentence Case | Excel Discussion (Misc queries) | |||
How do I change case to sentence case in groups in excel? | Excel Discussion (Misc queries) | |||
How to change case on a spreadsheet from Caps to Sentence? | Excel Discussion (Misc queries) | |||
Change Capitals to Sentence Case | Excel Discussion (Misc queries) | |||
Excell should have a sentence case function like the UPPER() and . | Excel Worksheet Functions |