UPPERCASE to Normal case?

[JazzSP8]

Hi all

I've got rows and rows of text in UPPERCASE, I need to be able to drop them all into Normal case.

I know there's UPPER/LOWER/PROPER in Excel but as far as I know nothing to change it to normal?

EG: "THIS IS TEXT. IT NEEDS CONVERTING." becomes "This is text. It needs converting."

I've tried searching but almost everything on here or Google seems to deal with UPPER/LOWER/PROPER - Or at least using the terms I've used does

A soloution in VBA or Formulas would work, either way

Thanks in advance

 

[Hermanito]

Maybe the VBA in this link helps:
http://www.suite101.com/content/microsoft-excel-change-text-to-sentence-case-a145878

 

[JazzSP8]

That's exactly what I needed! - Thanks Hermanito

 

[rs2k]

Heres another one by Simon Huggins;

Sub SentenceCase()
For Each cell In Selection.Cells
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

Cheers
Colin

 

[Hermanito]

Just thought of a slightly more complicated way to do this

- Create a reference to the Word object library
- copy the text you want to convert to Sentence case to a new Word document
- apply the native Word functionality to get Sentence case
- copy back the text

Or if it is a one-time thing, you might get away with just copying all your texts from Excel to Word, applying the Sentence case, and copying it all back manually

 

[JazzSP8]

Heres another one by Simon Huggins;

Thanks Colin, I note that deals with "?" as well - I don't need that this time but I've kept a note of it anyways in case it crops up

Just thought of a slightly more complicated way to do this

Yes, you did LOL

I completely forgot that Word did this to be honest, I used to know that but somehow it must have leaked out

I'd have been happy enough remembering the term 'Sentance Case' instead of searching using 'Normal Case'