REGEX TO EXTRACT DATA INSIDE BRACKETS WHICH PRECEEDS CERTAIN STRING

[Shahas]

Hi All,

I have this situation, where in data needs to be extracted from inside a bracket. There can be multiple brackets as well.

For eg : [abc-242] jsydg [xuys-285] jyagd [iayb-248] links to [brrwfej-342]

in this case, only data inside the bracket before "links to" needs to be taken. so output should be [iayb-248]

but, some data might be [eqb-231] uajyuvdb [fejo-758] kjahb. in this case, the last bracket needs to taken. ie, [fejo-758].

i tried with .*\[(.*)\] links to.* but it doesn't give desired the output if "Links to" is not present.

I also tried 

(?<=\[)([\w-]+?)\]

but it doesn't satisfy the criteria when "Links to" is present.

if anyone could provide a solution which satisfy all the condition, it would be really helpful.

Thanks in advance

[Shahas]

Hi @Christina_H ,

The solution is really good. But my data is inconsistent. Sometimes, "links to" can be followed up with some other text with any space.

For eg : "Links toAbc".

Can anything be done in this situation??

Thanks

[Christina_H]

@Shahas Is that stopping the formula working?  It's looking for "links to" which is still included.  The capital L might be an issue, you can try this to make it case insensitive
if Contains(LowerCase([Field1]),"links to") or Contains(LowerCase([Field1]),"risks to") then REGEX_Replace(Left([Field1],iif(Contains(LowerCase([Field1]),"risks to"),FindString(LowerCase([Field1]),"risks to"),FindString(LowerCase([Field1]),"links to"))),".*\[(.*)\].*","$1")
else Null() endif

[Shahas]

@Christina_H 

For eg : uwqdh [abc-852] Risks toRisks from [jydf-276] Risks from [nfw-827], the output should be [abc-852] but while using this formula, i'm getting its as [nfw-827]

Thanks

[OllieClarke · Accepted solution]

Hi @Shahas 

I think this is what you're after, but apologies if I've misread something:

If CONTAINS([1],'Risks to') THEN
REGEX_Replace([1],'.*(\[[^\]]+\]) Risks to.*','$1')
ELSEIF CONTAINS([1],'links to') THEN
REGEX_Replace([1],'.*(\[[^\]]+\]) links to.*','$1')
ELSE
REGEX_Replace([1],'.*(\[[^\]]+\]).*','$1')
ENDIF

So we pull out the square brackets immediately before " Risks to" (if it's there)

Or we pull out the square brackets immediately before " links to" (if it's there)

Or we pull out the final square brackets

Hope that helps,

Ollie

[Christina_H]

@Shahas For the version I sent above with case insensitivity, I get the correct output