Conditional extraction from a string between two characters

value name=“Stage”>B - program stage 1</values

I have a requirement to scan a string of which the above excerpt is buried. Every record will have this field in varying positions and value length.

What I need is to identify the string between the > and <. In this case “B- program stage 1” but only where the value name = “Stage”

I considered using Split but the characters are different and I could not find a why to attach it to the back of a conditional query to find “Stage”

Hello @DSTAN,

Can you see if this formula will work for you?

ifelse(startsWith(split(Col1,'“',2),"Stage"),split(split(Col1, '>', 2),'<',1),"Nope")

Kind regards,

Hey Andres, the formula runs but returns the False result of Nope for all records

This is the data I am using for testing, how does your data looks like?

sorry, I may have explained this poorly. each record looks something like this, the bit I am after is only a small part of each records


    locate(col1, '>', locate(col1, 'value name="Stage"') + strlen('value name="Stage"')) + 1,
    locate(col1, '<', locate(col1, '>', locate(col1, 'value name="Stage"') + strlen('value name="Stage"'))) -
    (locate(col1, '>', locate(col1, 'value name="Stage"') + strlen('value name="Stage"')) + 1)

Conditional extraction from a string between two characters


thank you @robdhondt that did it!