Regex_Replace Not Working When Regex Seems to be Good

[Scott_Wallace]

I have a text string of [Vendor ID]#0008021711# and I would like to use regex_replace to receive true or false using the following formula: REGEX_replace([1],'*\[Vendor ID\]#(.*?)','$1')='0008021711' I am getting False when the statement seems correct. I can use the Reg_Ex tool to parse the VEndor ID but ant seems to use replace or match. Additionally, I used Regex1001.com to verify and the statement seems correct.

[Luke_C]

Hi @Scott_Wallace 

Try changing the #1 to $1

[Scott_Wallace]

Changed, and updated the post. Still getting false.

[Luke_C]

How about something like this:

REGEX_replace([1],'.*#(\d+)#','$1') ='0008021711'

[Scott_Wallace]

What am I doing wrong?

[Luke_C]

Looks like you have leading trailing spaces (indicated by the red triangle in your results window). If thats true to what your data is try something like:

REGEX_replace(trim([1]),'.*#(\d+)#','$1') ='0008021711

[Scott_Wallace]

That worked, but I was providing just a portion for the string, thinking a solution for the portion would work for the whole string. Why does the formula not work for the following? Thoughts?

[Engagement ID]#214GGG00.00F#[YRMO]#2219#[Vendor ID]#0008021711#[RECNO]#2#

[Luke_C]

Will your vendor ID always be 10 digits? If so:

REGEX_replace(trim([1]),'.*#(\d{10})#.*','$1') ='0008021711'

[Scott_Wallace · Accepted solution]

Thank you for your response. I ended up using a RegEx_Match formula to return a boolean True or False. 

REGEX_MATCH([1],'.*vendor id.*0008021711.*','$1')

This seems to work. The goal was to transpose a set of data and look for text strings in the transposed data using the dynamic replace. I have the model up and running now and it seems to be working.