PROPER formula?

[Cyndi K.]

I have a spreadsheet and the data is in all capitals.
How do I change it to PROPER font?

 

[Frank Kabel]

Hi Cyndi
one way:
if your data is in column A add a helper column B and enter the
following in B1:
=PROPER(A1)
copy this formula down
After tthis select column B and copy it(CTRL+C). Now goto 'Edit -
Paste Special' and paste the copied range as 'Values' to remove the
formulas

 

[Don Guillett]

Put this in your personal.xls and use whenever desired.
Highlight (select) the cells to change and then execute.

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thank you so much!

-----Original Message-----
Hi Cyndi
one way:
if your data is in column A add a helper column B and enter the
following in B1:
=PROPER(A1)
copy this formula down
After tthis select column B and copy it(CTRL+C). Now goto 'Edit -
Paste Special' and paste the copied range as 'Values' to remove the
formulas

--
Regards
Frank Kabel
Frankfurt, Germany


.