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
Solved! Go to Solution.
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
@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
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
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