Proper case excluding state abbreviations

[Cardstang]

I have a string of text in cells that is in all CAPS and includes the address, city, and state of folks, and I'm using the Proper formula to convert it to a better format. It works well except that it also converts the state abbreviation.

For example,

300 E MAIN ST, RENO, NV
10897 W POPLAR AVE, ALLENDALE, MI

Ends up looking like:

300 E Main St , Reno, Nv
10897 W Poplar Ave, Allendale, Mi

It's all good except for the state abbreviations. That needs to remain all caps.

I have a search and replace running to convert it back, but it doesn't always catch everything, and sometimes it will convert something back that I don't want converted.

I could set up a series of actions to add a space at the end of each string and then perform the search/replace, but I'm thinking there is a better way.

Any suggestions?

Thanks!

[HansV]

If ALL addresses end with the state, you can use a formula like this:

=PROPER(LEFT(A1,LEN(A1)-2))&RIGHT(A1,2)

[ChrisGreaves]

It's all good except for the state abbreviations. That needs to remain all caps.

Edited to add the attachment.

I wrote my own Proper Case macro (attached).
Here is an example of its use (I have disabled the call to strGPA which reads a customised string from the INI file)

Code: Select all

Sub TESTstrProperCase()
    Dim strReference As String
'    strReference = strGPA(ThisDocument, strcProperTitleReference, strcProperTitleReferenceDefault)
    strReference = strcProperTitleReferenceDefault
    MsgBox strProperCase("you see!", strReference)
    MsgBox strProperCase("you see!", strReference, "_")
    MsgBox strProperCase("you see!", strReference, "")
    MsgBox strProperCase("you see!", "", "")
    MsgBox strProperCase("exclamation marks used to be frowned upon. now look what's happened! we use them all the time! hurrah!!! but what is it about the age of email that gets people so over-excited?", "")
    MsgBox strProperCase("exclamation marks used to be frowned upon. now look what's happened! we use them all the time! hurrah!!! but what is it about the age of email that gets people so over-excited?", strReference)
End Sub

[HansV]

Attached?

[Cardstang]

HansV,

Thanks for the formula. Worked just as I needed.

The other suggestion seems to be a bit overkill for what I'm needing. Perhaps when the attachment is included it will make more sense.

[ChrisGreaves]

Perhaps when the attachment is included it will make more sense.

It is now re-attached. My fault. I attached and uploaded the Module1.BAS file.
This board doesn't accept BAS files as attachments; they have to be renamed as TXT.

[HansV]

How is your code going to help Cardstang? It's meant for Word, not for Excel (it uses CheckSpelling).

[ChrisGreaves]

How is your code going to help Cardstang? It's meant for Word, not for Excel (it uses CheckSpelling).

My apologies on two counts:
(1) I didn't notice/check to see the office product being explored; I got caught up in the surprise of using my code for state abbreviations at the time I saw the post.
(2) I may have included too much code; I was heading out the door and just grabbed the appropriate module from a large utility application.

No excuses for #2, because I have a procedure-stripper that strips excess (deadwood) code from any project; Ten seconds on my part might have told me something.