Setting up capitalization after hyphens
 

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

Setting up capitalization after hyphens
 

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

Setting up capitalization after hyphens
 

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