cwcrogan Posted March 13, 2022 Share Posted March 13, 2022 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. Link to comment Share on other sites More sharing options...
comment Posted March 13, 2022 Share Posted March 13, 2022 (edited) 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, 2022 by comment Link to comment Share on other sites More sharing options...
cwcrogan Posted March 13, 2022 Author Share Posted March 13, 2022 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 774 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now