Discussions

Expand all | Collapse all

Extract Text Between 2 special characters

Jump to Best Answer
  • 1.  Extract Text Between 2 special characters

    Posted 21 days ago
    Edited by Gene Gene 21 days ago
    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
    ------------------------------


  • 2.  RE: Extract Text Between 2 special characters
    Best Answer

    Posted 21 days 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
    ------------------------------



  • 3.  RE: Extract Text Between 2 special characters

    Posted 21 days ago
    Awesome, that worked perfect. Do you mind just quickly explaining what you did in that formula?

    ------------------------------
    Gene Gene
    ------------------------------



  • 4.  RE: Extract Text Between 2 special characters

    Posted 21 days 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
    ------------------------------



  • 5.  RE: Extract Text Between 2 special characters

    Posted 21 days ago
    Thank you so much, Mark!!

    ------------------------------
    Gene Gene
    ------------------------------



  • 6.  RE: Extract Text Between 2 special characters

    Posted 20 days ago
    Edited by Gene Gene 20 days ago

    Mark, as I started to test more, how would I exclude running formula if the results show ERROR instead of number. How would include "If" to your formula?


    If
    >ERROR< 

    I don't want to formula to run.

    Basically, the Pipeline output will either be with a number, or ERROR.
    <?xml version="1.0" encoding="utf-x"?> <string xmlns="http://www.tempuri.org/">1234</string>
    <?xml version="1.0" encoding="utf-x"?> <string xmlns="http://www.tempuri.org/">ERROR</string>

    If number, then trim that into new field (which is how it's done, if ERROR, don't put anything to new field.



    ------------------------------
    Gene Gene
    ------------------------------



  • 7.  RE: Extract Text Between 2 special characters

    Posted 20 days ago
    np

    var text RawResult = Left(Right(NotRight([My Field],1),">"),"<");

    IF($RawResult <> "ERROR",  $RawResult)

    // the formula above uses a formula variable called RawResult to hold an intermediate calculation. A formula variable is like a field but it only has life within the formula box itself.
    https://help.quickbase.com/user-assistance/formula_variables.html


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



  • 8.  RE: Extract Text Between 2 special characters

    Posted 20 days ago
    Thank you, Mark!

    ------------------------------
    Gene Gene
    ------------------------------