I'm trying to clean up some existing data (about 10,000 records). I have an Address field which has primarily been filled with all caps (the entire db is actually, but attacking this piece by piece). So, I'd like to change

50A OAK STREET to 50A Oak Street

Replace field contents with Proper (Address) works great except that I'm left with 50a Oak Street. How can capitalize the 'a' after '50'? The number can be 1, 2, or 3 characters long - and in some rare cases, occurs after the street name.

Putting aside those rare cases, you could do something like:

LeftWords ( Address ; 1 ) & " " & Proper ( RightWords ( Address ; WordCount ( Address ) - 1 ) )

However, you need to be aware that Proper ( "O'HARE") returns "O'hare" and there is no way to tell if "MACDONALD"  should be "Macdonald" or "MacDonald". 

 

Edited March 13, 20223 yr by comment

Aaaahhh, very good. Thank you!

I was trying to address the problem I created after using Proper, but as you show, should have thought about a broader approach.

The rare cases can be ignored. And I have list of street names and can easily correct the couple of mid-word cap letter (DelCarmine, MacDonald) with Substitute.

Thanks again, comment