7 Replies Latest reply on Jun 7, 2016 1:56 PM by James_L

    Extracting Text From Field: Advanced

    James_L

      I've imported a massive data-set, and I have a field:

      Order_Detail

      that contains data most often in this form:

       

      55555  8019 Elm (Peggy)

       

      where I want to break the various parts apart into separate fields.


      (1) The leftmost data (5555): no problem.  LeftWord snared it up to the first space. 

       

      (2) The  contact name "Peggy": no problem: the parentheses made  that easy.

       

      (3)  The municipal address "8019": I cannot seem to capture that.

       

      (4) The street name: "Elm."  I cannot seem to capture that.

       

      Suggestions?   Bonus points if it doesn't matter whether there's one space or two between the various parts: data-entry was uneven.

        • 1. Re: Extracting Text From Field: Advanced
          Mike_Mitchell

          Try:

           

          Let ( [

               valueAsList = Substitute ( Order_Detail ; [ " " ; "¶" ] ; ["¶¶" ; "¶" ] ; [ "¶¶" ; "¶" ] ) ;

               streetNumber = GetValue ( valueAsList ; 2 ) ;

               streetName = GetValue ( valueAsList ; 3 )

          ] ;

           

          {insert either streetNumber or streetName, depending on what you need}

           

          )

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Extracting Text From Field: Advanced
            erolst

            MiddleWords ( string ; 2 ; 1 )

             

            MiddleWords ( string ; 3 ; 1 )

            • 3. Re: Extracting Text From Field: Advanced
              Mike_Mitchell

              Being aware that neither solution will work if there's more than one word in the street name. If that's the case, you should probably forego the GetValue method and modify the MiddleWords count to WordCount - 3.

              • 4. Re: Extracting Text From Field: Advanced
                jurgmay

                I'd make a temporary custom function and feed in the field value of 'Order_Detail' as a parameter. In my example the parameter is 'theText'.

                 

                Let ( [

                 

                    firstWord = LeftWords ( theText ; 1 ) ; // Get the first value.

                 

                    theText = Trim ( Middle ( theText ; Length ( firstWord ) + 1  ; Length ( theText )  )  ) ; // Remove the first value from theText and trim any spaces.

                 

                    municipalAddress = MiddleWords ( theText ; 1 ; 1 ) ; // Get the municipal address.

                 

                    theText = Trim ( Middle ( theText ; Length ( municipalAddress ) + 1  ; Length ( theText )  )  ) ; // Remove the municipal address from theText and trim any spaces.

                 

                    charPos = Position ( theText ; "(" ; Length ( theText )  ; -1 ) ; // Starting from the right find the first occurence of a (

                 

                    contactName = Middle ( theText ; charPos ; Length ( theText ) - charPos + 1 ) ; // Get the contact name.

                 

                    streetName = Trim ( Left ( theText ; charPos - 1 ) ) // Remove the contact name, trim any spaces. The remaining text is the street name regardless of word count.

                 

                    ] ;

                 

                    List ( firstWord ; municipalAddress; streetName ; contactName ) // Return the values as a list. Do whatever you like here.

                 

                )

                 

                It can probably be optimised a bit but it does the job, regardless of the number of words in the street name. I've commented the code so you can see what's going on.

                 

                Note the sequence in which the values are extracted. We remove the first value, then the second value, then the last value in the () and then whatever is left is the street name.

                2 of 2 people found this helpful
                • 5. Re: Extracting Text From Field: Advanced
                  dtcgnet

                  This is a fairly long formula, but it takes into account streets with more than one word (e.g., River Elm), data with only 1 parameter (either street or number), and addresses with a fraction as part of the number (e.g., 8019 1/2).

                   

                  You should be able to copy and paste it directly. In the x_String line, replace the string with the Order_Detail field. You'd use this formula, as is for all four components: 1) ZIP code, 2) Name, 3) numerical portion of street address, 4) street name. At the bottom of this formula is "x_NumberPortion". Just replace that in each formula with the component you want (x_zip, x_name, x_NumberPortion, or x_StreetPortion.

                   

                  Give that a whirl.

                   

                  Let (

                  [

                     x_String = "55555  8019 1/2 River Elm (Peggy)" ; //(Use Table::Order_Detail instead)

                     x_zip = LeftWords ( x_String ; 1 ) ;

                     x_name = RightWords ( x_String; 1 ) ;

                     x_leftFirstSpace = Position ( x_String ; " " ; 1 ; 1 ) ;

                     x_rightFirstLeftParen = Position ( x_String ; "(" ; Length ( x_String ) ; -1 ) ;

                     x_middlePortion = Trim ( Middle ( x_String ; x_leftFirstSpace ; x_rightFirstLeftParen - x_leftFirstSpace ) ) ;

                     x_WordCountInStreetAddress = WordCount ( x_middlePortion ) ;

                     x_slashInStreetAddress = PatternCount ( x_middlePortion ; "/" ) ;

                   

                   

                     x_NumberPortion =

                        Case (

                                  x_WordCountInStreetAddress = 1 ; "Missing Data" ;

                                  x_WordCountInStreetAddress = 2 ; LeftWords ( x_middlePortion ; 1 ) ;

                                  x_WordCountInStreetAddress > 2 and x_slashInStreetAddress ≥ 1 ; LeftWords ( x_middlePortion ; 2 ) ;

                                  x_WordCountInStreetAddress > 2 and x_slashInStreetAddress = 0 ; LeftWords ( x_middlePortion ; 1 ) ;

                                 ) ;

                   

                   

                     x_StreetPortion =

                        Case (

                                  x_WordCountInStreetAddress = 1 ; "Missing Data" ;

                                  x_WordCountInStreetAddress = 2 ; RightWords ( x_middlePortion ; 1 ) ;

                                  x_WordCountInStreetAddress > 2 and x_slashInStreetAddress ≥ 1 ; RightWords ( x_middlePortion ; x_WordCountInStreetAddress - 2 ) ;

                                  x_WordCountInStreetAddress > 2 and x_slashInStreetAddress = 0 ; RightWords ( x_middlePortion ; x_WordCountInStreetAddress - 1 ) ;

                                 )

                   

                   

                    ] ;

                   

                   

                  x_NumberPortion

                  )

                  • 6. Re: Extracting Text From Field: Advanced
                    dtcgnet

                    The formula I posted would not return the correct name if the name was something like "Peggy Ann". It would return just "Ann".

                     

                    This could be modified a bit for ease of reading, but it ought to work pretty well.

                     

                    Let (

                    [

                       x_String = "55555  8019 1/2 River Elm Road (Peggy Sue Ann)" ; //(Use Table::Order_Detail instead)

                       x_zip = LeftWords ( x_String ; 1 ) ;

                       x_name = Middle ( x_String ; Position ( x_String ; "(" ; 1 ; 1 ) + 1 ; Length ( x_String ) - Position ( x_String ; "(" ; 1 ; 1 ) - 1 ) ;

                       x_leftFirstSpace = Position ( x_String ; " " ; 1 ; 1 ) ;

                       x_rightFirstLeftParen = Position ( x_String ; "(" ; Length ( x_String ) ; -1 ) ;

                       x_middlePortion = Trim ( Middle ( x_String ; x_leftFirstSpace ; x_rightFirstLeftParen - x_leftFirstSpace ) ) ;

                       x_WordCountInStreetAddress = WordCount ( x_middlePortion ) ;

                       x_slashInStreetAddress = PatternCount ( x_middlePortion ; "/" ) ;

                     

                     

                       x_NumberPortion =

                          Case (

                                    x_WordCountInStreetAddress = 1 ; "Missing Data" ;

                                    x_WordCountInStreetAddress = 2 ; LeftWords ( x_middlePortion ; 1 ) ;

                                    x_WordCountInStreetAddress > 2 and x_slashInStreetAddress ≥ 1 ; LeftWords ( x_middlePortion ; 2 ) ;

                                    x_WordCountInStreetAddress > 2 and x_slashInStreetAddress = 0 ; LeftWords ( x_middlePortion ; 1 ) ;

                                   ) ;

                     

                     

                       x_StreetPortion =

                          Case (

                                    x_WordCountInStreetAddress = 1 ; "Missing Data" ;

                                    x_WordCountInStreetAddress = 2 ; RightWords ( x_middlePortion ; 1 ) ;

                                    x_WordCountInStreetAddress > 2 and x_slashInStreetAddress ≥ 1 ; RightWords ( x_middlePortion ; x_WordCountInStreetAddress - 2 ) ;

                                    x_WordCountInStreetAddress > 2 and x_slashInStreetAddress = 0 ; RightWords ( x_middlePortion ; x_WordCountInStreetAddress - 1 ) ;

                                   )

                     

                     

                      ] ;

                     

                     

                    x_Name

                    )

                    1 of 1 people found this helpful
                    • 7. Re: Extracting Text From Field: Advanced
                      James_L

                      Oh, wow.  That nails it: elegant and versatile.  (I even made a drop-down menu so I can change the parameter of what's asked for and use it to sort... not that I need it, but because, hey, why not.)

                       

                      Thank you SO much.  Works like a charm.