Transforming many cells of all caps into first letter capitals onl

[Jbm]

I have approximately 600 cells in a single column, with each word in each
cell in all caps. It looks something like this:
A
HARDWOOD FLOORS
CARPETING
OAK FLOORS

etc.

I would like to make it so my data looks like this
A
Hardwood Floors
Carpeting
Oak Floors

Simply, the first letter of each word capitalized instead of everything in
all caps. Since it's 600 cells, I need to do this en masse without having to
retype every cell. Any help?

 

[Jbm]

Forgot to say what version I'm running: Excel 2007.

 

[Mike H]

Hi,

I'm not entirely certain how you enter code in Excel 2007 but try this
anyway. Right click your sheet tab and click 'View Code' and paste the code
below in on the right hand side. Select the column of text and run the code

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

Mike

 

[Jbm]

Thanks a lot Mike, that worked perfectly. Saved me a lot of time and I
learned something new!

 

[Mike H]

Glad I could help

 

[Gord Dibben]

Assuming data is in column A

In B1 enter =PROPER(A1)

Double-click the fill handle to copy down.

Select Column B and copy.

Select A1 and Edit>Paste Special>Values>OK>Esc.


Gord Dibben MS Excel MVP