Forum Discussion

GeneStrelkov's avatar
GeneStrelkov
Qrew Cadet
4 years ago

Extract Text Between 2 special characters

I've been going through several posts here all morning, trying to find how I would extract text that's between 2 characters, and I can't seem to figure out. 

I have the following field text:
<?xml version="1.0" encoding="utf-x"?> <string xmlns="http://www.tempuri.org/">1234</string>

I need to extract the number that's between 2nd ">", and 3rd "<" (in the example, that number is 1234, and it always changes, and after 9999, it will turn into 5 character number.)

The closest I could come up, was:
Part([URL],3,">")

But the result comes out to:
1234</string

And I don't need "<" and anything that is after.

------------------------------
Gene Gene
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    4 years ago
    Try this

    Left(Right(NotRight([My Field],1),">"),"<")

    It should work as long as your field always ends with    >99999</string>



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Try this

    Left(Right(NotRight([My Field],1),">"),"<")

    It should work as long as your field always ends with    >99999</string>



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • GeneStrelkov's avatar
      GeneStrelkov
      Qrew Cadet
      4 years ago
      Awesome, that worked perfect. Do you mind just quickly explaining what you did in that formula?

      ------------------------------
      Gene Gene
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        :) sure, you build these from the inside out as you iterate the formula.  I did not do this in one pass.

        Left(Right(NotRight([My Field],1),">"),"<")

        NotRight([My Field],1)  // this lops off the last character (Not Right) as I saw it was a > and  I later would need to extract up to the > character but not counting that last >.
        That would have given this
        <?xml version="1.0" encoding="utf-x"?> <string xmlns="http://www.tempuri.org/">1234</string

        Then next step to wrap that with a Right formula was this 
        Right(NotRight([My Field],1),">")

        That says to take the Right of the string (think pacman eating from the right) so far but stop at the first >
        that would have given this
        1234</string>

        Then we wrap that in Left

        Left(Right(NotRight([My Field],1),">"),"<")

        so that will come at the string now from the left and pacman up to the first <  thus leaving 
        1234

        It is good that you're asking and what I suggest you do is go ahead and remake that formula in pieces starting from the inside out as I described and then observe the string getting reduced either from the left or the right until you are just left with 1234


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------