Using substitute to adjust format of addresses.
We have a field which contains a manually entered list of places in the following format:
Example = Place One, 123 address street, Philadelphia, PA 19111 or Place Two, 456 address street, Camden, NJ 08026.
I am trying to change the format to
123 address street
Philadelphia, PA 19111
456 address street
Camden, NJ 08026
Using the Substitute function, I have been able to replace all instances of ", " with a carriage return. However that results in the city and state being put on separate lines.
This looks odd when reading the form we put this information on.
The state abbreviation is always in capitals. Is there any way to have substitute for ", " only happen when not followed by two capital letters? I have not been able to figure out how to test for that.
I have a different way I can handle this but due to the nature of our structure, it is much more involved than the substitution I am hoping to accomplish.