Changing text from all caps to first letter cap

G

Guest

I have a file with alot of columns of data. In a couple of the columns, the
words are typed with all caps. I would like the words to have only the first
letter of each word capitalized. I can not do an edit/replace because there
are many different words. Is there a formatting or edit/replace trick that I
can use to accomplish this? Any information is appreciated.

Thanks
 
M

Myrna Larson

In a 'helper' column put a formula like =PROPER(A1). Copy down, then copy this
column and Edit/Paste Special,Values over the original data.
 
G

Gord Dibben

Dave

To change one cell to Proper Text use =PROPER(cellref)

This can be copied to encompass a range of columns and rows.

Assume the two columns are A and B

In C1 enter =PROPER(A1)

Darg/Copy to D1 then select C1 and D1 and drag/copy down.

When happy, Copy column C and D and Paste Special(in place)>Values>OK>Esc.

Delete column A and B

OR you could use a macro to do all without formulas. If so desired, see David
McRitchie's site for his Proper macro.

http://www.mvps.org/dmcritchie/excel/proper.htm


Gord Dibben 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

Similar Threads

fix all caps text to first letter caps 1
Transforming many cells of all caps into first letter capitals onl 5
changing ALL CAPS 6
Change all text on Spread sheet 1
How To Remove all caps in column? 5
paragraph initial words in small caps 1
excel sheet all caps and needs to be only the first letter caps.. 1
Changing from ALL CAPS 2

Top