3 Replies Latest reply on Oct 11, 2015 12:29 PM by Daniel Vincent

    Help with this text formula




      Can anyone help me with a formula to clean up Field 2 so that I just get State Name as desired. The format is always the same, will have a comma after state name.



      Field 2Desired Field 2
      New York, United States)New York
      Illinois, United States)Illinois
      Texas, United States)Texas




        • 1. Re: Help with this text formula
          Daniel Vincent

          This should work for you LEFT([Field 2],(FIND([Field 2],','))-1)

          • 2. Re: Help with this text formula
            Daniel Vincent

            Another way (aside from a REGEXP), if you're using 9 or higher, is to split it from edit data page.  Especially useful if that string has a full address and you know what the delimiters are. 


            Screen Shot 2015-10-11 at 2.15.52 PM.png

            Screen Shot 2015-10-11 at 2.16.33 PM.png

            Screen Shot 2015-10-11 at 2.16.40 PM.png

            • 3. Re: Help with this text formula
              Daniel Vincent

              And finally, just to see if I can get REGEXP to do the same search:


              REGEXP_EXTRACT([Field 2],'(Alabama|Alaska|Arizona|Arkansas|California|Colorado|Connecticut|Delaware|Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|Michigan|Minnesota|Mississippi|Missouri|Montana|Nebraska|Nevada|New\sHampshire|New\sJersey|New\sMexico|New\sYork|North\sCarolina|North\sDakota|Ohio|Oklahoma|Oregon|Pennsylvania|Rhode\sIsland|South\sCarolina|South\sDakota|Tennessee|Texas|Utah|Vermont|Virginia|Washington|West\sVirginia|Wisconsin|Wyoming)')


              I'm sure if there is a way to look for the delimiter from right to left (from country, zip, state) that would be cleaner.