Transforming many cells of all caps into first letter capitals onl

J

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?
 
M

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
 
J

Jbm

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top