How do i turn all text to Proper Text

[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

 

[Mike H]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Rick Rothstein]

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.

 

[Rick Rothstein]

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...

 

[Gord Dibben]

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

 

[Rick Rothstein]

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.

 

[Gord Dibben]

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


Gord

 

[Rick Rothstein]

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.

 

[Gord Dibben]

Yup


Gord