How do I convert data in Excel 97 from Capitals to proper letters.

[Guest]

How do I convert data in Excel 97 from Capitals to proper letters... e.g.
(from AUSTRALIA to Australia)

I have tried using "Proper" function but it only converts one cell at a
time, I want to convert a column or multiple columns or rows at once or may
be the whole worksheet.

 

[Rojee]

Simply copy the formula ...you may also drag the formula
down

 

[Rojee]

Simply copy the formula ...you may also drag the formula
down

 

[icestationzbra]

select a range of cells wherein you want this change of cas
implemented. run the following macro:

Option Explicit

Sub ProperCase()

Dim rng As Range

If Selection.Cells.Count < 2 Then Exit Sub

For Each rng In Selection

rng.Value = UCase(Left(rng.Value, 1)) & LCase(Mid(rng.Value, 2
Len(rng.Value)))

Next rng

End Sub

see if that helps

 

[icestationzbra]

slight change to incorporate numeric validation:

Option Explicit

Sub ProperCase()

Dim rng As Range

If Selection.Cells.Count < 2 Then Exit Sub

For Each rng In Selection

If Not IsNumeric(rng.Value) Or Not IsNumeric(Left(rng.Value, 1)) Then

rng.Value = UCase(Left(rng.Value, 1)) & LCase(Mid(rng.Value, 2
Len(rng.Value)))

End If

Next rng

End Su

 

[Dave Peterson]

Just a note:

VBA's Mid is different from the worksheet function =mid(). You don't need 3
arguments:

... & lcase(mid(rng.value,2))

would have sufficed.

(and it's less typing--so fewer things can go worng!)

 

[David McRitchie]

The poster asked for Proper Case not Sentence Case based on
the =PROPER(...) formula doing the job ok for the one cell.
A macro solution was certainly on the right track though.

Suggest looking at
http://www.mvps.org/dmcritchie/excel/proper.htm

BTW, if sentence case really is wanted it has to capitalize the next
word after a period. Tushar Mehta has a macro that does that very
well using Regular Expression see related area of the above.