6 Replies Latest reply on Jun 4, 2014 8:04 AM by raybaudi

    Help with Text Function

    john.s

      Title

      Help with Text Function

      Post

           Hey,

           I received a file from a school that contains the City, State and Zip all in one field.  I need to break it into three fields.  I  can get the Zip using Right function…all zips are 5 digits...and State using a combination of Middle and Right but can not figure out how to pull the City. I need to pull all characters from the left up to the comma as the City.

           Data looks like this:

           New Haven, CT 06123

           Durham, CT 06321

           Monroe, NY 10950

           any help will be greatly appreciated.

           John

        • 1. Re: Help with Text Function
          mleering
          My inclination would be to use the substitute function to substitute pilcrows(hard returns) wherever commas are found. Provided there's only one comma, this will work nicely, allowing you to then Getvalue(substitutedtext ;1) for the city
          • 2. Re: Help with Text Function
            RickWhitelaw

                 I'm not at a computer right now, and can't try this, but what about Right(address field; -7) or -8 if the space must be counted? I have no idea if this would work.

            • 3. Re: Help with Text Function
              john.s

                   Thank you for your help.  Matthew, the substitute function along with the Getvalue work like a charm.

                   Rick, I also tried the Right text function with -10 but could not get it to work.  I don't understand why…it seems like a logical answer.

                    

                   Thanks again for your help…you saved me a lot of time.

                    

                   John

              • 4. Re: Help with Text Function
                raybaudi

                " I also tried the Right text function with -10 but could not get it to work."

                     Because you should use the Left( ) function, something like:

                     Left ( YourText ; Length ( YourText ) - 10 )

                • 5. Re: Help with Text Function
                  john.s

                       Raybundi's solution also works using the Left text function.   Unfortunately I can only mark one answer as correct.

                       Thanks for helping work out the solution using just the Text function without the Substitute.  They both do the job

                        

                       John

                  • 6. Re: Help with Text Function
                    raybaudi

                    This is notmy solution!
                    It 's justthe explanation ofwhy,usingthe Right () function, it did not work.I would haveused the samesolution proposed byMatthew.