How to get PROPER to maje exceoptions

[Minitman]

Greetings,

I am using the PROPER function in a formula for street names. Here is
the snippet of code in question:

...&IF(AB2<>0,Z2&" "&PROPER(AB2),"")&...

Works for everything but the numbered streets (eg. 12th street becomes
12Th Street and 3rd Street becomes 3Rd Street). Is there any way for
the "th" or the "rd" to remain lower case and the "Street" to be
PROPER?

Any help would be appreciated.

TIA

-Minitman

 

[Gord Dibben]

Minitman

Can you use a macro?

Sub Exception_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Dim sStr As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
' cell.Formula = Application.Proper(cell.Formula)
sStr = Application.WorksheetFunction.Proper(cell.Formula)
sStr = Application.Substitute(sStr, " Of ", " of ")
sStr = Application.Substitute(sStr, " Is ", " is ")
sStr = Application.Substitute(sStr, " And ", " and ")
sStr = Application.Substitute(sStr, " A ", " a ")
sStr = Application.Substitute(sStr, " The ", " the ")
sStr = Application.Substitute(sStr, " An ", " an ")
sStr = Application.Substitute(sStr, "Th ", "th ")
sStr = Application.Substitute(sStr, "Nd ", "nd ")
sStr = Application.Substitute(sStr, "Rd ", "rd ")
cell.Formula = sStr
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP

 

[David McRitchie]

The VBA function evidently does not have that
problem. For a macro that does a bit more
like helping with most sur=ames that would
be incorrectly capitalized with PROPER see
http://www.mvps.org/dmcritchie/excel/proper.htm

Cell.Formula = StrConv(Cell.Formula,vbProperCase)