Loading ...
Sorry, an error occurred while loading the content.

21863Re: add commas in address string

Expand Messages
  • flo.gehrke
    Jul 1, 2011
    • 0 Attachment
      --- In ntb-clips@yahoogroups.com, Mike Breiding - Morgantown WV <mike@...> wrote:
      >
      >
      > Postal code:
      > I need to replace the space in front of the first number with a comma
      >
      > State abbreviation:
      > I need to replace the space in front of the first letter with a comma
      >
      > City :
      > I need to replace the space in front of the first letter with a comma
      >
      >
      > 125 N. Main Street City ST 11111
      >
      > Then I will paste into Excel

      I looked it up in Wiki and found that the US Postal Code is a nine-digit ZIP code, separated with '-' between position #5 and #6, and there are two spaces between state code and ZIP code -- isn't it?

      That is, in order to change a list of addresses like...

      1500 E. Main Ave Springfield VA 22262-1010
      1500 E. Main Ave Springfield VA 22262-1010
      1500 E. Main Ave Springfield VA 22262-1010

      to...

      1500 E. Main Ave,Springfield,VA,22262-1010
      1500 E. Main Ave,Springfield,VA,22262-1010
      1500 E. Main Ave,Springfield,VA,22262-1010

      you could try...

      ^!Replace "(?x-i)\x20{1,2} ( ((?=\d{5}-\d{4}$)) | ((?=[[:upper:]]{2},\d{5}-\d{4}$)) | ((?=\w{1,},[[:upper:]]{2},\d{5}-\d{4}$)) )" >> "," WARS
      ; End of long line
      ^!IfError End
      ^!Goto Skip_-2

      It's written in Extended Mode to make the subpatterns more visible.

      Maybe it's more readable if we split the long alternation into three command lines...

      ; Match two spaces in front of ZIP code
      ^!Replace "\x20{2}(?=\d{5}-\d{4}$)" >> "," WARS
      ; Match one space in front of state code
      ^!Replace "(?-i)\x20(?=[[:upper:]]{2},\d{5})" >> "," WARS
      ; Match one space in front of city
      ^!Replace "(?-i)\x20(?=\w{1,},[[:upper:]]{2},\d{5})" >> "," WARS

      Regards,
      Flo
    • Show all 25 messages in this topic