8 Replies Latest reply on Oct 4, 2015 7:31 AM by KenDeFilipps

    Text Parsing question


      I have a text string I am trying to split up and place into two separate fields.  I have tried parsing and filtering, but not having much success.


      The raw text string is placed into a field called Rawtext and has the form below including commas and parens

      ((43.039598867970355, -76.1742639541626), (43.054056917882434, -76.16726875305176))


      I need to parse out the first and third segments of numbers and place into another field called TexSeg1 in this form


      And then do the same for the second and fourth segment into field called TexSeg2 in this form


      These numbers can be of varying length so position counts won't work.  Left words, right words is giving me varying results, none of which is what I'm after.

      Can someone suggest a method to accomplish this task?  BTW, this text string is being used to create a search.  So there is only one record in this table and the fields are all Global in nature.  There will never be any more than one record in this table.  The Rawtext field is overwritten each time I need to use it.

      Thanks for any help you can offer.

        • 1. Re: Text Parsing question

          Hi there...


          This could do it... See attached file, but what I've done is to create two calculation fields for TexSeg1 and TexSeg2 that uses the Let, Filter, Substitute and GetValue functions... Basically the calculations use Filter to strip out the parentheses and spaces from RawText (result only has '0123456789-,.' characters (note the minus sign, comma, and period are included), then Substitute the commas with paragraph breaks, which gives us a list of the 4 values. Then finally uses GetValue to get items 1 & 3, and 2 & 4 of that list to create the final output with "..." inserted in between....


          For example, TexSeg1 is calculated as:


          Let ( [

          filtered = Substitute ( Filter ( Rawtext ; "1234567890.,-" ) ; "," ; "¶" )

          ] ;

          GetValue ( filtered ; 1 ) & "..." & GetValue ( filtered ; 3 )




          Take a look at the attached file and see if it makes sense :-)


          cheers, Sky.

          • 2. Re: Text Parsing question

            Give this a shot for TexSeg1


            Let (


            ~RawValues = Substitute ( RawText ; [ "((" ; "" ] ; [ "(" ; "" ] ; [ "))" ; "" ] ; [ ")" ; "" ] ; [ "," ; "¶" ] ; [ " " ; "" ] )

            ] ;

            GetValue ( ~RawValues ; 1 ) & "..." & GetValue ( ~RawValues ; 3 )



            Copy that and change the 1 and 3 to 2 and 4 for TexSeg2.


            Make sure the result of your calculation is text.


            In the above, we're taking out all of the parentheses and spaces and changing the comma to a return character. It'll give you four "values" from your RawText.

            • 3. Re: Text Parsing question

              My apologies. It looks like Sky's most likely uses the same method.

              • 4. Re: Text Parsing question

                Ha - yep - very similar :-)

                • 5. Re: Text Parsing question

                  As others have said...dang the lack of refresh! I posted mine and there yours was. I like the efficiency of using "Filter" as you did. Very nice!

                  • 6. Re: Text Parsing question

                    One other thing...it sure looks like you've got two latitude and longitude specifications. 1 & 3 are probably the latitude range and 2 & 4 are probably the longitude range (or vice versa). I'd probably opt for names that reference that more clearly, if that's what's really going on here. LatitudeRange and LongitudeRange or something like that just to help any developer down the road easily see what you're up to.

                    • 7. Re: Text Parsing question

                      Hi Sky.  Perfect.  That did the trick.  As far as it making sense, not yet.  However, there does appear to be a certain elegance to its complexity   I'll keep chewing on it until the light bulb turns on.

                      Thanks again.

                      • 8. Re: Text Parsing question

                        @dtcgnet Yes, agreed. In reality the fields are NewLat and NewLng, but for the sake of simplicity and clarity in forming my question, I thought it best not to muddy the waters with the brain stew that is Latitude and Longitude.

                        And thanks for your reply.  Sky's solution did the trick, but your response was also on target.