Proper(text) function on entire worksheet?

[Bill D]

Hi,

I've acquired a huge Excel (2002) file which I'd like to use in a mailmerge
but unfortunately all text entered previously is in uppercase. I've found
the function Proper() which corrects uppercase to first letter as a capital
then the rest as lowercase, but.... it appears that it can only be used to
convert individual cells into a new/adjacent cell (i.e not simply convert
the existing cell.

Can anyone help me overcome this problem please?

Cheers

Bill

 

[Nick Hodge]

Bill

You could use this code and then select the data you wish to convert. You
should be aware that Proper does have some limitations with McDonald for
example becoming Mcdonald

Sub ConvertToProper()
Dim myCell As Range

For Each myCell In Selection.SpecialCells(xlCellTypeConstants)
myCell.Value = Application.Proper(myCell.Value)
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Bill D]

Sorry Nick but you are obviously mistaking me for someone that knows more
than a village idiot about Excel.... unless of course I've accidentaly
posted in a 'Japanese for Beginners' newsgroup! ;o)

What on earth is all of that code and where does it go?

Off to hunt out those paracetamol in the meantime......

Bill

 

[David McRitchie]

Hi Bill,
See Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
to put the macro that Nick suggested into a regular module.

If you want a version that will change macdonald to MacDonald
see my page
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Nick Hodge]

David

Will I get sued for putting McDonald instead of MacDonald... we have those
too ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Bill D]

Cheers guys.... what a pair of stars you are!!!

Worked Perfectly on all 5,000 records - saved me a lifetime of work too!!

Thanks again.

Bill