How to get PROPER to make exceptions

[Minitman]

Greetings,

*Please excuse the misspelled subject of the earlier post*

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

 

[Harlan Grove]

...
...

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?

No way to make PROPER work this way. Is there any reason you can't change the
addresses in your column AB to the mixed case you want? If you selected this
whole range, you could then run the macro


Sub foo()
Dim c As Range, i As Long

If Not TypeOf Selection Is Range Then Exit Sub

On Error GoTo CleanUp

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each c In Selection
c.Value = Application.WorksheetFunction.Proper(c.Value)
Next c

Selection.Replace What:="1St", Replacement:="1st", _
LookAt:=xlPart, MatchCase:=True

Selection.Replace What:="2Nd", Replacement:="2nd", _
LookAt:=xlPart, MatchCase:=True

Selection.Replace What:="3Rd", Replacement:="3rd", _
LookAt:=xlPart, MatchCase:=True

For i = 0 To 9
Selection.Replace What:=Format(i, "0\T\h"), _
Replacement:=Format(i, "0\t\h"), LookAt:=xlPart, MatchCase:=True
Next i

CleanUp:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Minitman]

Hey Harlan,

I will have to accept your statement at face value "No way to make
PROPER work this way..." and come up with something totally different.
A macro won't work in this case since this is an on going project with
a lot more entries to be made.

You have given me an idea that could solve the problem. I will break
off the numbered and superscripts from the rest of the name for
formatting and recombine them for the reference field.

Thanks for the reply.

-Minitman

 

[Rob]

Hi,

I think you will be able to combine PROPER with SUBSTITUTE to achieve what
you want, however, I'm unsure how to go about combining the formulas -
sorry.

Rob

 

[Harlan Grove]

I think you will be able to combine PROPER with SUBSTITUTE to
achieve what you want, however, I'm unsure how to go about combining
the formulas - sorry.

....

Not reliably in all instances. The problem with using SUBSTITUTE is nested
function calls. You'd need to replace the following:

0Th -> 0th
[1]1Th -> [1]1th
1St -> 1st
[1]2Th -> [1]2th
2Nd -> 2nd
[1]3Th -> [1]3th
3Rd -> 3rd
4Th -> 4th
5Th -> 5th
6Th -> 6th
7Th -> 7th
8Th -> 8th
9Th -> 9th

That's 13 SUBSTITUTE calls to be sure you've changed all ordinal suffixes,
and they'd need to be nested. Can't be done.

 

[Minitman]

Hey Harlan,

Thanks for trying, I guess it wasn't meant to be. <G>

-Minitman

I think you will be able to combine PROPER with SUBSTITUTE to
achieve what you want, however, I'm unsure how to go about combining
the formulas - sorry.

...

Not reliably in all instances. The problem with using SUBSTITUTE is nested
function calls. You'd need to replace the following:

0Th -> 0th
[1]1Th -> [1]1th
1St -> 1st
[1]2Th -> [1]2th
2Nd -> 2nd
[1]3Th -> [1]3th
3Rd -> 3rd
4Th -> 4th
5Th -> 5th
6Th -> 6th
7Th -> 7th
8Th -> 8th
9Th -> 9th

That's 13 SUBSTITUTE calls to be sure you've changed all ordinal suffixes,
and they'd need to be nested. Can't be done.

 

[Rob]

I think the following does what you want so long as the ordinal (1st etc.)
has a space after it.

Regards, Rob

=LOWER(LEFT(A1,FIND(" ",A1,1)))&PROPER(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

Hey Harlan,

Thanks for trying, I guess it wasn't meant to be. <G>

-Minitman

I think you will be able to combine PROPER with SUBSTITUTE to
achieve what you want, however, I'm unsure how to go about combining
the formulas - sorry.

...

Not reliably in all instances. The problem with using SUBSTITUTE is nested
function calls. You'd need to replace the following:

0Th -> 0th
[1]1Th -> [1]1th
1St -> 1st
[1]2Th -> [1]2th
2Nd -> 2nd
[1]3Th -> [1]3th
3Rd -> 3rd
4Th -> 4th
5Th -> 5th
6Th -> 6th
7Th -> 7th
8Th -> 8th
9Th -> 9th

That's 13 SUBSTITUTE calls to be sure you've changed all ordinal suffixes,
and they'd need to be nested. Can't be done.

 

[David McRitchie]

Hi Minitman,
I don't see why you started another thread see my
answer in your other thread in this group or just use my macros
in http://www.mvps.org/dmcritchie/excel/proper.htm

 

[Harlan Grove]

I think the following does what you want so long as the ordinal (1st etc.)
has a space after it.

Regards, Rob

=LOWER(LEFT(A1,FIND(" ",A1,1)))&PROPER(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

...

Your assumption needs to be stronger: the numbered street name would need to be
the first 'word' in the cell. That is, your formula would convert '5Th Street'
to '5th Street' and '123-5Th Street' to '123-5th Street', but it's no help for
'123 5Th Street' or '987 East 19Th Avenue'.

There's no way to do this *ROBUSTLY* and *GENERALLY* with formulas using only
built-in functions. Once VBA becomes a possibility, a udf could handle this
easily. If the OP were to use one that makes use of the VBScript regular
expression object, such as the Subst function in

http://www.google.com/[email protected]

then it could be used to handle this problem as

=Subst(PROPER(Subst(A1,"(\d)(th|st|nd|rd)\b","$1#X$2")),"#X","")

which adds a dummy string before the ordinal suffixes that is acted on by
PROPER, then removes the dummy string.