1 2 Previous Next 20 Replies Latest reply on Oct 24, 2012 2:43 PM by paulatkins

    Extracting text from a field.

    paulatkins

      Hi,

       

      I have a name field supplied by a school in this format:

       

      CAFFIN-HOUGHTON, Eleanor

      (last name comma firstname)

       

      I need a LastName field and a FirstName field from the data.

       

      I have used LeftWords and RightWords, but it does not handle the hyphenated names and I have plenty of both first and last names that are hypenated. Can I extract the text to the left and right of the comma?

       

      Thank you,

       

      Paul

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

          yes! POSITION() is your friendly neighborhood function!

           

          name_last = Left ( fullname ; Postition ( fullname; "," ; 1 ; 1 ) - 1 )

          name_first = Right ( fullname ; Length ( fullname ) - Position ( fullname ; ", " ; 1 ; 1 ) - 1 )

           

           

          Let ( 
             [ fullname = "CAFFIN-HOUGHTON, Eleanor"
             ; comma = Position ( fullname; ", "; 1 ; 1 )
             ; $name_last = Left ( fullname; comma - 1 )
             ; $name_first = Right ( fullname ; Length ( fullname ) - comma - 1 )
             ]; fullname
          )
          

           

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Extracting text from a field.
            IanWilson

            Hi Paul:

             

            Not a problem. Use the Position() function to find the position of the character ",". Add 1 and then substract that from the result of the Length() function. That gives you the number of characters of the first name. Now use the Right() function with the result of the previous calc. That will return the first name. You can apply the same logic and use the Left() function to extract the substring to the left of the "," character.

             

            Or Substitute() the "," with a "[pilcrow]". This turns the contents of the original field into a paragraph delimited value list. You can then use the GetValue() function to extract either the first, or second values.

             

            Cheers

            Ian

            1 of 1 people found this helpful
            • 3. Re: Extracting text from a field.
              paulatkins

              Thanks Beverly,

              The Let statement sets the variables $name_last and $name_first, I can see that happening. But my skills to insert the variables into the field are lacking...I've trolled through the help and the forum since you posted, but can't find the syntax.

              I am showing my true colours now!

              Thank you

              Paul

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

                Thank you Ian, I like the second Substitute idea, that could be very helpful.

                I have asked Beverly for further help with the syntax to apply the "Let", and I will work back from that to nut out your substitute suggestion.

                 

                Cheers,

                 

                Paul

                • 5. Re: Extracting text from a field.
                  beverly

                  Paul, I wrote the calculations two ways. One is each of the fields as calculations. The other is the "let()" to set both at once - this could be used in a script. You probably want the calculations (as auto-enter in the "name_last" & "name_first" fields, not as calculated fields):

                   

                  LastName = Left ( name ; Postition ( name; "," ; 1 ; 1 ) - 1 )

                  FirstName = Right ( name ; Length ( name ) - Position ( name ; ", " ; 1 ; 1 ) - 1 )

                   

                  As the "name" field gets populated (import? data entry?), then the other two fields will AUTOMATICALLY be filled in.

                   

                  If you already have the name field and need to populate the other two (from previously entered/imported data), then you may wish to use the Let() in a script:

                  Go to Record, First
                  Loop
                       Set Variable  $names = Let ( 
                    [ fullname = yourtable::name
                    ; comma = Position ( fullname; ", "; 1 ; 1 )
                    ; $name_last = Left ( fullname; comma - 1 )
                    ; $name_first = Right ( fullname ; Length ( fullname ) - comma - 1 )
                    ]; fullname
                  )
                          Set Field ( LastName, $name_last )
                       Set Field ( FirstName, $name_first )
                       Exit Loop If   Get ( RecordNumber ) = Get ( FoundCount )
                       Go To Record   Next
                  End Loop
                  

                  HTH,

                  Beverly

                  • 6. Re: Extracting text from a field.
                    paulatkins

                    Ha!

                    Right before my eyes, thank you.

                    What tripped my was your statement:

                    LastName = Left ( name ; Postition ( name; "," ; 1 ; 1 ) - 1 )

                    It couldn't find the command "Postition", so I assumed I had to use the Let. But i realised you and I have the same issue: occasionally swapping letters around, I did not see it as incorrect!

                    Thank you again, I'm on my merry way.

                    Paul

                    • 7. Re: Extracting text from a field.
                      paulatkins

                      And this raises another small problem, but I think I may have to manually sort this out in the data export because i can envisage a rather large calculation!

                       

                      The calcualtion works well, but because the school provided the last name in 'all caps', when i use 'Title Case' to make it upper and lower case, it ignores the capitalisation on the second hyphenated word!

                       

                      CAFFIN-HOUGHTON, Eleanor

                      Eleanor Caffin-houghton

                       

                      Is there an easy style for format choice, or do I need to engage a developer?

                       

                      Thank you,

                       

                      Paul

                      • 8. Re: Extracting text from a field.
                        beverly

                        Take a look at the Proper() function. Help topics give examples of how to use the functions. They even spell them correctly. LOL. You can "nest" functions.

                         

                        -- sent from my iPhone4 --

                        Beverly Voth

                        --

                        • 9. Re: Extracting text from a field.
                          comment

                          Be very careful here, because  capitalizing text takes away real information and it's difficult to put it back reliably -  try for example =

                           

                          Proper ( "MCCARTNEY" )

                           

                          I recall someone* has spent a lot of time and effort trying to develop an "intelligent" system to deal with all possible gotchas - but of course that requires making some assumptions that aren't always true.

                           

                           

                           

                          ---

                          http://www.foundationdbs.com/downloads.html

                           

                           

                           

                          Message was edited by: Michael Horak

                          • 10. Re: Extracting text from a field.
                            paulatkins

                            Interestingly, if there are spaces either side of the hyphen (between the names):

                            CAFFIN - HOUGHTON

                            It formats fine

                            Caffin - Houghton


                            • 11. Re: Extracting text from a field.
                              comment

                              It's a question of what counts as a word separator and in what context. Filemaker's calculation engine considers "CAFFIN-HOUGHTON" as two words, but Filemaker's text-styling engine sees it as one only.

                              • 12. Re: Extracting text from a field.
                                beverly

                                Perhaps you can convert the "-" to " - " (space dash space), convert to Proper, convert the " - " back to just "-"?

                                 

                                Beverly

                                • 13. Re: Extracting text from a field.
                                  comment

                                  Beverly Voth wrote:

                                   

                                  Perhaps you can convert the "-" to " - " (space dash space), convert to Proper, convert the " - " back to just "-"?

                                   

                                  It would make no difference.

                                  • 14. Re: Extracting text from a field.
                                    beverly

                                    I'm sorry, I was sure the OP stated that " - " ( note the spa es) between words allowed the Proper() to work as expected. It won't compensate for the McD... from MCD..., but the quest was for hyphenated names. I didn't have a chance to test, did you?

                                     

                                    -- sent from my iPhone4 --

                                    Beverly Voth

                                    --

                                    1 2 Previous Next