AnsweredAssumed Answered

Using substitute to adjust format of addresses.

Question asked by ErichWetzel on Jul 8, 2009
Latest reply on Jul 9, 2009 by comment_1


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


Place Two

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.  



PA 19111


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.