Conditional extraction from a string between two characters

Question & Answer
, , , ,
1

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”

2

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,
Andres.

3

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

4

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

image
image919×196 15.1 KB

5

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
image

6

c_locate_stage:

substring(
    col1,
    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)
)

Arena:
Conditional extraction from a string between two characters

2 Likes
7

thank you @robdhondt that did it!