How do i turn all text to Proper Text

  • Thread starter Thread starter Withnails
  • Start date Start date
W

Withnails

Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you
 
Hi,

Right click your sheet tab, view code and paste the code below in. Select
the column/row or block of text and run the code

Sub proper()
For Each c In Selection
c.Formula = WorksheetFunction.proper(c.Formula)
Next c
End Sub

Mike
 
Sub Macro()
For Each cell In ActiveSheet.UsedRange
cell.Value = StrConv(cell, vbProperCase)
Next
End Sub

If this post helps click Yes
 
1) Here is a method that does use a loop...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Value = Application.Proper(.Value)
End With
End Sub

Just change my example worksheet name ("Sheet3") to the actual name of the
worksheet that you want to do this on.

2) Same set up except we change the Cells reference to the Column
reference...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Columns("E")
.Value = Application.Proper(.Value)
End With
End Sub

Change the worksheet name as in #1 above and also change the column letter
from my example of "E" to the actual column letter or number.
 
1) Here is a method that does use a loop...

The line above should have said...

1) Here is a method that does NOT use a loop...
 
Jacob

I would trap for formulas so's if any they don't get converted to values.

Sub Macro()
For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = StrConv(cell, vbProperCase)
End If
Next
End Sub


Gord Dibben MS Excel MVP
 
Warning: The code I posted has the same defect that Gord pointed out in
Jacob's code... it will convert formulas to text, so you may not want to use
what I posted.

I'm not sure I can salvage a non-looping method given this problem, but I
will look at it to see.
 
Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Formula = Application.Proper(.Formula)
End With
End Sub


Gord
 
I was going to post that, but it also has a potential hidden problem... any
quoted text within a formula will be changed to proper case.
 
Back
Top