How to capitalize the first letter of every word in a cell in excel

[natatbh]

Is there any way I can set a column in an excel sheet so that the first letter of every word typed into a cell will become capital automatically?

Thanks, natatbh

 

[SkipVought]

Hi,

You can use the worksheet function PROPER to create a new column of values.

If you want the original column affected then you'll have to copy the new column and Paste Special - Values into the original column.

Otherwise you need a VBA procedure.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[natatbh]

Thanks Skip,

I'd appreciate if you can give me an idea of how to do the following. I have a column that has all its cells filled with text. I whould like to create another column with exactly the same text as the original column but with the addition of using the worksheet function PROPER. I then want all the changes made by the proper function to show in the original column using vba. any idea? please help.

Thanks
natatbh

 

[SkipVought]

If your text is in Column A then in Row 1, say Column D...

=PROPER(A1)

Then Copy Column D and Paste Special/Values in Column A.

Or a VBA solution (select a cell in the column where your text is and run this)...

Sub MakeProper()
  With Selection
    Row1 = .CurrentRegion.Row
    Row2 = .CurrentRegion.Rows.Count + Row1 - 1
    col = .Column
  End With
  For Each r In Range(Cells(Row1, col), Cells(Row2, col))
     r.Value = Application.Proper(r.Value)
  Next
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com