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

Title

Using substitute to adjust format of addresses.

Post

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

 

Place

123 address street

Philadelphia, PA 19111

or

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.  

 

Philadelphia

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. 

 

Thanks

-Erich 

Outcomes