6 Replies Latest reply on Aug 2, 2015 3:41 AM by Shrab

    Plucking segments from a string

    DLW-BPEX

      Title

      Plucking segments from a string

      Post

      There must be a text function (or combination) to achieve this result:

      What is a good way to parse a string like ABCDE|FGH|IjklmnO|...

       

      so that for one field I can capture all characters up to but excluding the first | ("pipe") character? Next field, only everything between the first and second pipes. Third field, only what is between the second and third pipes, etc. Entire text string can vary in length, as can each desired subset.

       

      Thank you, as always.

      David 

        • 1. Re: Plucking segments from a string
          philmodjunk
            

          You can use the leftWords and MiddleWords functions to get what you want.

           

          LeftWords(text; 1) will return all characters to the left of the first "|" in text

           

          MiddleWords( text; 2; 1)

           

          will return the text between the 1st and 2nd "|" characters.

           

          Edit note: changed functions when I relized the | character was recognized as a word separator.

          • 2. Re: Plucking segments from a string
            DLW-BPEX
               Thanks, Phil.
            • 3. Re: Plucking segments from a string
              DLW-BPEX
                

              Thanks, Phil.

              Ordinarily that would be an even better, simpler solution. In fact, it worked perfectly at first.

              But in case others are following this, I wanted to point out that in this solution the text string represents user-defined Project Identifiers (text based), and we need reasonably to expect the user will include space, hyphen, #, and possibly other characters that also act as word separators. In that event, the function stops too soon.

               

              Of course we could require the Project I.D. to be strictly numeric and get around it, but I am afraid that would be too restrictive; e.g., 101-A, #445, HR Training, etc. might be desired input.

               

              Thanks again,

              David 

              • 4. Re: Plucking segments from a string
                philmodjunk
                  

                Which makes my original, pre-edit suggestion the better approach. Reposting it for anyone reading this thread:

                 

                Left (text; Position(text; "|" ; 1 ; 1)  - 1) will return text left of the first |

                 

                Middle ( text; Position ( text ; Position ( text ; "|" ; 1 ; 1 ) + 1) ; Position ( text ; "|" ; 1 ; 2 ) - Position ( text ; "|" ; 1 ; 1 ) ) will return text between the 1st and 2nd |.

                • 5. Re: Plucking segments from a string
                  iceknight

                       Hi,

                       I was reading this one and wondered if this is the best method to solve my issue, im trying to look for a word in a text field and return that text.

                       Example: 

                       Fields:  Discription and Item

                       Discription:

                       Please order 15 pens
                       I will need a black biro

                       Item:
                       I want it to extract "pen" or "biro"

                        


                        

                  • 6. Re: Plucking segments from a string
                    Shrab

                    HI

                    not sure if this thread is still being followed or updated....

                     

                    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

                     

                     

                    Condo

                                            3 Bedrooms, 

                                            1 Bathrooms, 

                                            Size 91

                     

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