4 Replies Latest reply on Dec 14, 2011 7:34 AM by GeoffreyMartin3300

    A calc to strip a portion of text in a field?

    GeoffreyMartin3300

      Title

      A calc to strip a portion of text in a field?

      Post

      I have imported data that is created by another system that adds a city code onto the end of an address.  It's a 3 letter code that always starts with a "-" tagged on the street address.  Is there a way to strip that off?  I was trying to use the FilterText Calculation but am not sure the verbage to get what I am after.  Maybe I'm using the wrong calculation too.  Any ideas?

        • 1. Re: A calc to strip a portion of text in a field?
          johnhorner

          try this (where "Text" is the name of your address field):

          Left ( Text ; Position ( Text ; "-" ; 1 ; PatternCount ( Text ; "-" ) ) -1 )

          it won't matter how many characters follow the "-" or if there are other "-"'s in the address before the final one.

          • 2. Re: A calc to strip a portion of text in a field?
            GeoffreyMartin3300

             Thank you.  It worked like a champ.  If you don't mind, can you take a minute to explain what that did so I can better learn how to use it in the future. 

            • 3. Re: A calc to strip a portion of text in a field?
              johnhorner

              sure... (i think?)

               

              so i start with the "left function which has the following format: Left ( text ; numberOfCharacters ).  this function will return first X number of characters you specify from the left side of whichever field (text) you specify.  when i write a calculation, for me, i start by selecting it from the function list and then begin replacing... it is easier for me to think in terms of one thing at a time. so step 1 looks exactly like the left function itself:

              Left ( text ; numberOfCharacters ).

              next, i simply select "text", and then find the field i want to use (in your case the address field), so step 2 would look like this:

              Left ( TABLEOCCURRENCE::Address ; numberOfCharacters )

              now it gets confusing even for me.  to know how many characters i want, i need to find where in the string the "-" is located.  for this i used the position function which has a format like this: Position ( text ; searchString ; start ; occurrence ).  so, my next step is to insert this function into my calculation so it looks like this:

              Left ( TABLEOCCURRENCE::Address ; Position ( text ; searchString ; start ; occurrence ) )

              now i start to replace the generic placeholders in the position function with my actual values.  in the position function "text is the text you are searching in (your address field again), "searchString" is the text (which i think can be any number of characters) that you are trying to locate (in your case, "-"), in filemaker help it defines "start" as: "any numeric expression, or field containing a number, representing the number of characters from the start of the text string at which to begin the search".  i have always wanted to start at the beginning so i use "1" (i don;t know if the same result would occur if you put in "0"?).  i also don't know if the value it returns is the position in the entire text minus the start position, or if the start position is used to skip over instances of the searchstring that occur at a know location earlier in the text? finally, "occurrence", specifies whether you want the position of the 1st, 2nd, 3rd,... etc instance of the search string.  if you knew for a fact there there was only one occurenece of your search string you could safely use "1".  so, for now, the next stage of creating our calculation looks like this:

              Left ( TABLEOCCURRENCE::Address ; Position ( TABLEOCCURRENCE::Address ; "-" ; 1 ; occurrence ) )

              i didn't replace occurrence yet, because it occurred to me that perhaps in your address there might be another "-", perhaps in the zip code?  but since we don't know whether it is there or not, we need to count how many there actually are and use that for our occurrence value (i.e. if there are 2 "-"'s, we need occurrence to equal "2").  for this i used the patterncount function which will tell you how many times a given strng of text appears in a larger body of text.  so now our calculation looks like this:

              Left ( TABLEOCCURRENCE::Address ; Position ( TABLEOCCURRENCE::Address ; "-" ; 1 ; PatternCount ( text ; searchString ) ) )

              replacing the generic placeholders we get:

              Left ( TABLEOCCURRENCE::Address ; Position ( TABLEOCCURRENCE::Address ; "-" ; 1 ; PatternCount ( TABLEOCCURRENCE::Address ; "-" ) ) )

              if the final "-" is at 15 characters, the calculation above will give us the first 15 characters in your address field.  however, 15 characters would include the "-" so we really only want 14 characters so we simply subtract one from the expression that is giving use the numberOfCharacters for the "left" function (perhaps it might have been easier to do this at the beginning (i.e. Left ( text ; numberOfCharacters -1 ), but usually i don't realize that this will be a problem until i see the results and hten i have to go back and adjust.  so our final calculation is:

              Left ( TABLEOCCURRENCE::Address ; Position ( TABLEOCCURRENCE::Address ; "-" ; 1 ; PatternCount ( TABLEOCCURRENCE::Address ; "-" ) ) -1 )

              i am by no means an expert so there may be some instances where this calculation might throw you off... actually, now that i think of it... assuming your extra coding is always "-" plus 3 characters, you could simplify your calculation by using the "length" function to tell you how many characters you wanted to return in the "left".  for example:

              Left ( TABLEOCCURRENCE::Address ; Length ( TABLEOCCURRENCE::Address ) - 4 )

              but if in the future your extra code expanded or contracted, it would not function properly.

               

              hope that is helpful... good luck!

              • 4. Re: A calc to strip a portion of text in a field?
                GeoffreyMartin3300

                 Wow, that was awesome.  I appreciate it.  Sometimes the Filemaker Help is a little vague and that was certainly not!  Thanks a lot. I'm sure I will use this in the future.

                -Geoff