Hi all:

I have a spreadsheet which lists 20,000 last names - all in lower
case. Some of these last names are hyphenated -- "smith-jones".

I would like to automatically capitalize the first letter and the
letter after the hyphen.

I can do the former using ASAP Utilities (a great plug-in!) but
haven't a clue as to how I capitalize the first letter after the
hyphen.

Any ideas on what to do?


Thanks,
SB

smith-jones in A1

=PROPER(A1) in B1 returns Smith-Jones

Is that what you want?

A macro would do the job without the formulas.

Sub Proper_Case()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rng Is Nothing Then
Else
rng.Formula = Application.Proper(rng.Formula)
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 3 Mar 2008 14:01:06 -0800 (PST), wrote:

Hi all:

I have a spreadsheet which lists 20,000 last names - all in lower
case. Some of these last names are hyphenated -- "smith-jones".

I would like to automatically capitalize the first letter and the
letter after the hyphen.

I can do the former using ASAP Utilities (a great plug-in!) but
haven't a clue as to how I capitalize the first letter after the
hyphen.

Any ideas on what to do?


Thanks,
SB

Thanks very much. That did the trick!!

SB



On Mar 3, 2:19 pm, Gord Dibben <gorddibbATshawDOTca wrote:
smith-jones in A1

=PROPER(A1) in B1 returns Smith-Jones

Is that what you want?

A macro would do the job without the formulas.

Sub Proper_Case()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rng Is Nothing Then
Else
rng.Formula = Application.Proper(rng.Formula)
End If
End Sub

Gord Dibben MS Excel MVP

On Mon, 3 Mar 2008 14:01:06 -0800 (PST), wrote:
Hi all:

I have a spreadsheet which lists 20,000 last names - all in lower
case. Some of these last names are hyphenated -- "smith-jones".

I would like to automatically capitalize the first letter and the
letter after the hyphen.

I can do the former using ASAP Utilities (a great plug-in!) but
haven't a clue as to how I capitalize the first letter after the
hyphen.

Any ideas on what to do?

Thanks,
SB