4 Replies Latest reply on Jul 3, 2012 10:39 PM by Abhaya

    Separate text field values

    dlh27

      Title

      Separate text field values

      Post

      Hoping FMPro can solve a problem.  I've successfully concatenated text fields, but now I need to separate a field (or whatever the opposite of concatenate is).  I have  a column which contains values consisting of one or more words and an expression that begins with a "#".  For Example - Soccer Complex #01.

      I've successfully used the "Right" function to extract the "#" expression.

      I can't figure out how to isolate the beginning of the expression.  There isn't a consistent number of letters or words to work with.  There are 4 possible values that appear before the "#":

      UT Dallas

      Moss Park

      Toyota of Lewisville - Railroad Park

      Chinn Chapel

      Any help would be appreciated.

        • 1. Re: Separate text field values
          davidanders

          If only four possibilities that will not change.

          A Case Statement would be easy.
          If the first letter is U, set the field to "UT Dallas"



          http://www.mightydata.com/blog/parsing-like-a-pro/
          Here are the steps I go through in order to figure out what my calculation will look like:

          1. Review multiple samples of the data to be parsed. If you only use one example, you’re bound to miss the nuances of “more complex” examples.
          2. Look for patterns. Ask yourself, “If I had to do this manually, what would I be looking for?” Look for things like:
            1. X always starts with Y
            2. There is always an X before/after Y (where X could be a comma, semicolon, colon, etc.)
            3. X will always be Y number of characters
          3. Don’t focus too much on extraneous stuff. What I mean is there are great tools in FileMaker to filter (hint, it’s the “Filter” function!) text down to the “wanted” characters.
          4. Love and respect the “Let” function. The beauty of the let function is that it lets you take parsing one step at a time, with minimal confusion. See the examples below for proof. Also, use “white space” or spaces/returns within your calculation so that it’s easy to re-read your calculation later, along with comments to explain what’s going on.
          5. Test, test, test. In most cases, the Data Viewer is the best way to parse out text so you can see your calculation working against live data.

          Here is a list of common functions when parsing data:

          • Left or LeftWords
          • Right or RightWords
          • Middle or MiddleWords
          • Position
          • Filter
          • Substitute
          • PatternCount
          • Length

           

          http://www.filemaker.com/help/html/func_ref3.33.44.html#1031282
          Home
          > Reference > Functions reference > Text functions

          Text functions
          Text functions can be used to analyze, rearrange, extract, and build text strings. For example, you could use the MiddleWords function to extract specific words from supplied text.
          Text functions operate on these parameters:
            •
          fields of type text
            •
          text constants (in quotation marks)
            •
          expressions having a text result
          Click a function name for details.

           
           
          This function
          Returns
          Char
          Returns the characters for the Unicode code points in the number.
          Code
          Returns the Unicode code points for the characters in the text. If zero characters are in the text, returns 0.
          Exact
          1 (True) for an exact match, or 0 (False) for a mismatch between two text strings or container fields.
          Filter
          Only the specified characters, in the order that they were originally entered in the text.
          FilterValues
          Only the specified values, in the order that they were originally entered in the text.
          GetAsCSS
          The specified text, converted to the CSS (Cascading Style Sheets) format.
          GetAsDate
          Dates in the specified text as field type date, for use in formulas involving dates or date functions.
          GetAsNumber
          Numbers in the specified text as field type number, for use with formulas involving numbers or numeric functions.
          GetAsSVG
          The specified text, converted to the SVG (Scalable Vector Graphics) format.
          GetAsText
          The specified number, date, time or timestamp as field type text, for use with formulas involving text or text functions.
          GetAsTime
          Times or timestamps in the specified text as field type time, for use with formulas involving the time or timestamp functions.
          GetAsTimestamp
          The specified data as field type timestamp, for use with formulas involving timestamps.
          GetAsURLEncoded
          The specified text, converted with URL (Uniform Resource Locators) encoding.
          GetValue
          A specific value from a list of values.
          Hiragana
          Hiragana converted from Katakana (Hankaku and Zenkaku).
          KanaHankaku
          Hankaku Katakana converted from Zenkaku Katakana.
          KanaZenkaku
          Zenkaku Katakana converted from Hankaku Katakana.
          KanjiNumeral
          Kanji numerals converted from Arabic numerals.
          Katakana
          Zenkaku Katakana converted from Hiragana.
          Left
          The specified number of characters in the text, counting from the left.
          LeftValues
          The specified number of values in the text, counting from the left.
          LeftWords
          The specified number of words in the text, counting from the left.
          Length
          The number of characters in the specified text, including all spaces, numbers, and special characters.
          Lower
          All letters in the specified text as lowercase.
          Middle
          The specified number of characters in the text, starting at a specified character position.
          MiddleValues
          The specified number of values in the text, starting with a specified value.
          MiddleWords
          The specified number of words in the text, starting with a specified word.
          NumToJText
          Roman numbers converted from Japanese text.
          PatternCount
          The number of occurrences of a search string in the specified text.
          Position
          The specified occurrence of a search string, starting from a specified position.
          Proper
          The first letter of each word in the specified text as uppercase, and all other letters as lowercase.
          Quote
          The specified text surrounded by quotation marks (“ ”).
          Replace
          A new string of characters consisting of the specified text as modified by the specified replacement text.
          Right
          The specified number of characters in the text, counting from the right.
          RightValues
          The specified number of values in the text, counting from the right.
          RightWords
          The specified number of words in the text, counting from the right.
          RomanHankaku
          Hankaku (alphanumeric & symbols) converted from Zenkaku (alphanumeric & symbols).
          RomanZenkaku
          Zenkaku (alphanumeric & symbols) converted from Hankaku (alphanumeric & symbols).
          SerialIncrement
          The combined text and numbers in a specified value, with the numbers incremented by the specified amount.
          Substitute
          A text string with every occurrence of a specified search string in the text replaced by a specified replacement string.
          Trim
          Text stripped of all leading and trailing spaces.
          TrimAll
          Text with full width spaces between non-Roman and Roman characters removed.
          Upper
          All letters in the specified text as uppercase.
          ValueCount
          A count of the total number of values in the specified text.
          WordCount
          A count of the total number of words in the specified text.
          • 2. Re: Separate text field values
            JimMac

            Try the Position function to find the # then extract the "beginning" string.

            Position(text;searchString;start;occurrence)
            Example "UT Dallas #01" is the Textfield, then the Position of "#" is 11
            then use the Left function to get the first 10 characters such as:
            Left(Textfield, Position(Textfield;"#";1;1) -1)  will return "UT Dallas "  Note blank after Dallas.  If you don't like the trailing blank use Trim function.
            Then use Right funtion to get the "#01"
            Right(Textfield, Length(Textfield)-Position(Textfield;"#";1;1) )  will return "#01"
            • 3. Re: Separate text field values
              FrankFitzpatrick

              Just a thought. If the number of characters from the # to the end are always the same, Use an if/then statement and the Right function to delete the last X number of characters and paste the results into your previously created New Field.

              • 4. Re: Separate text field values
                Abhaya

                HI

                 

                If this text fields/values are static and its last characters including "#" are same then go for Frank's solution.

                If  this values are dynamic then go for Jim's answer.Actually dynamic solutions are the best and we generally go for this for better performance.

                 

                Thanks