changing ALL CAPS

[bubba1965]

Is there a way to change all words in an excel document that are AL
CAPS to just normal case with just the first letter of each wor
capitalized - without having to do a find-replace for each and ever
word

 

[Frank Kabel]

Hi
you may have a look at
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Judy Freed]

I think you need the PROPER function. See this link:

http://www.uncc.edu/sysdev/HowTos/Excel/The PROPER Function.htm


Judy Freed

 

[Rollin_Again]

I am pretty sure the proper function can only be applied to specifc
cells and not the entire worksheet. If you want to convert the entire
sheet you will need a small VBA macro. Here is the code I have created
for you. Let me know if you need help figuring out how to add the code
and run it. The code applies to a certain range of cells so you'll
need to change it so that it covers your particular range.



Public Sub ProperCase()

Dim vCell As Range

For Each vCell In Range("A1:Z100")
If vCell.Value <> "" Then vCell = StrConv(vCell, vbProperCase)

Next vCell

End Sub



Rollin

 

[bubba1965]

Thanks everyone,

The proper function seems to work reliably

Rollin, the macro works however it changes words that were formatte
correctly to incorrect. For example, all lowercase words now have th
first letter capitalized and some words that already had the firs
letter capitalized - now change to lowercase.
Maybe I am doing something wrong

 

[Rollin_Again]

Hmmm.....that sounds strange. I tested the macro on various data (al
lowercase, all uppercase, mixed case, etc) that I had manually plugge
in and I could not seem to reproduce what you are decribing. Mayb
someone else may know why it is doing that?

Rolli

 

[David McRitchie]

Is there a way to change all words in an excel document

Seems to me you got more or less what you asked for. Rollins
macro was restricted to A1:Z100 And by document I presume
you mean the active worksheet as opposed to the entire workbook.

There are several considerations that I would look for or include in a
macro. I would make the macro work on a selection so if you only
wanted to do column B that is all it would do rather than use an
specific range coded in the macro. I would not bother with cells
outside of the used range (SpecialCells), and would limit the
scope to Text cells avoiding Formulas and numbers (SpecialCells).

With Proper there are some other things that I personally would
include because a macro is being run those include fixing last
name like my own. Leaving certain internal words as lowercase
"Once Upon a Time" rather than "Once Upon A Time".

You can see my example in
http://www.mvps.org/dmcritchie/excel/proper.htm

The page is well annotated.