4 Replies Latest reply on Aug 2, 2015 3:50 AM by Shrab

    Extracting a letter from a text field



      Extracting a letter from a text field


      I have a field containing GPS coordinates: start::Location=N49 30 18.0 W120 40 51.1

       "49" = LeftWords ( Right ( trails::location_Start ; Length ( trails::location_Start ) - 1 ) ; 1 )

      "120" = ?


      1 Is there a way to extract each coordinate (degrees, min,sec) value, minus the letters, store in a temporary field, without having to use a table?

      2 What is the formula to extract W120 without the letter?

      3 I would be open to more efficient ways of doing this.


      The field containing gps coordinates is a text field and the format is N## ## ##.# W### ## ##.# 

        • 1. Re: Extracting a letter from a text field

          Hi David, there are 6 words.

          For the 120, try:  Filter ( MiddleWords ( trails::location_Start ; 4 ; 1 )  ; "0123456789" )

          N49 = Filter ( LeftWords ( trails::location_Start ; 1 )  ; "0123456789" )

          30 = MiddleWords ( trails::location_Start ; 2 ; 1 )

          ... some might ask why I am not using GetAsNumber to remove the alpha ... it is because I do not know if N004 or W020 is valid (leading zero).  Filter() will preserve the leading zero which would be lost using GetAsNumber().

          • 2. Re: Extracting a letter from a text field

            FMP provides many ways to parse text and nested functions can massage the data into submission. For your instance I would filter the text to remove the alpha characters, but include a period to maintain the decimals and a space to maintain the structure, if you want your text parsed out into individual fields then substitute a pilcrow (the backwards P or return character) for the spaces, this creates a value list just like the list function, and then you can use any number of text functions to put the individual pieces into separate fields. It would look like this:

               MiddleValues ( Substitute ( Filter ( start::Location ; 1234567890 & " " & ".") ; " " ; ¶ ) ; 3 ; 1 )       this would give you the third value, 18.0

            You could use this function for each piece of data just change the next to last value from the right, in this case the 3, in a new field. Result is text.

            • 3. Re: Extracting a letter from a text field

              Using xWords, the decimals are maintained within each word; no need to add protections here.  And the desire is to pull each 'word' value separately, as I understand it, so including a space to maintain the structure would not be necessary.  You are tripling the calculation load.

              Just an FYI,

              Filter ( start::Location ; 1234567890 & " " & ".")  ... can be written as
              Filter ( start::Location ; "1234567890 ." )


              • 4. Re: Extracting a letter from a text field

                Hi Guys' how about extracting this scenario? 

                I have similar situation which I really could do with help...


                I want to extract the data shown in red and placed in a sepearate field from the string below; this is exactly how the data is currently presented




                                        3 Bedrooms, 

                                        1 Bathrooms, 

                                        Size 91


                Any help or suggestions of how this can be achieved are gratefully welcomed