5 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Return the position of a specific string

    baobaodou _

      I have a text line separated by space that looks like below:

       

      AB CD EF GH IJ KL MN OP QR ST

       

      Is there a way to get the position of, say 'GH', in the above text line? And the position I want is relative to other strings separated by space. In this case, the position of 'GH' is 4.

       

      Thanks for your help.

        • Return the position of a specific string
          Grant Perkins

          Hi baobaodou,

           

           

          You have a few functions to consider, to be used in a calculated field for extracting the data you need.

           

          If the string you want is always in the 4th 'block' and the blocks are separated by spaces you could consider using the LSPLIT function - see the Help file for full examples of usage.

           

          If the string you want is not always the 4th block but exists somewhere on the line and and is the only occurrence of that string (or the first occurrence of the string is the one you want) then you should also consider the option to use the INSTR function as part of a formula to extract the data string you require. This is often used in conjunction with the SUBSTR function. INSTR will help to identify the start position for the string to be extracted using SUBSTR. Again the Help file has full examples for both.

           

          See if these help and let us know how you get on.

           

          Grant

          • Return the position of a specific string
            baobaodou _

            Thanks for the detail write-up, Grant. I think what I really want is more like a VLOOKUP in Excel.

             

            For Example:

             

            Column1   Column2    Column3

            AB         ...          1

            CD         ...          2

            EF         ...          3

            GH         ...          4

             

            2 varibles are created to contain column1 and column2. By knowing the position of 'GH' in the 1st variable, I can get the value (4) in the 2nd varible. Right now what I figured out is to select all these 4 rows and use if...then to solve the problem. But these rows sometimes get very long and Monarch has a limitation (4000 characters) on how much could be selected in one report.

             

            Originally posted by Grant Perkins:

            Hi baobaodou,

             

             

            You have a few functions to consider, to be used in a calculated field for extracting the data you need.

             

            If the string you want is always in the 4th 'block' and the blocks are separated by spaces you could consider using the LSPLIT function - see the Help file for full examples of usage.

             

            If the string you want is not always the 4th block but exists somewhere on the line and and is the only occurrence of that string (or the first occurrence of the string is the one you want) then you should also consider the option to use the INSTR function as part of a formula to extract the data string you require. This is often used in conjunction with the SUBSTR function. INSTR will help to identify the start position for the string to be extracted using SUBSTR. Again the Help file has full examples for both.

             

            See if these help and let us know how you get on.

             

            Grant /b[/quote]

            • Return the position of a specific string
              Grant Perkins

              Originally posted by baobaodou:

              [font="courier"]Column1   Column2    Column3

              AB         ...          1

              CD         ...          2

              EF         ...          3

              GH         ...          4[/font][/quote][/b][/quote]So are you dealing with rows or columns? If columns, how many columns might there be?

               

              Can you use the utility (or something that does a similar job) to refropmet the data to make it more usable?

               

               

              Grant

              • Return the position of a specific string
                Data Kruncher

                to refropmet the data[/quote]Grant, can you please take a moment to instruct us on how to refropmet data? It sounds very interesting.

                 

                Sorry buddy, I know I'll pay for it somehow, someday, but I couldn't let it go.      

                • Return the position of a specific string
                  Grant Perkins

                  Originally posted by Data Kruncher:

                    /size[quote]quote:[/size]to refropmet the data[/quote]Grant, can you please take a moment to instruct us on how to refropmet data? It sounds very interesting.

                   

                  Sorry buddy, I know I'll pay for it somehow, someday, but I couldn't let it go.            /b[/size][/QUOTE]Must be the internet going via some foreign country that speaketh not english - everything was fine when it left here ....   

                   

                  As to the answer - just hack it with an editor or sumthin' ....

                   

                   

                  Grant