Regex Match and Replace words

[mem2243]

Hello,

I have a field with different objectives. When the field contains 'Long Term Growth', I want to change it to 'Long-Term-Growth'. The formula below only works if Long Term Growth is the only word. Please help. Thanks

Ex.

Safety, Income, Long Term Growth

if REGEX_Match( [Financial Objectives],"Long Term Growth",'') then "Long-Term-Growth"
elseif REGEX_Match( [Financial Objectives],"Maximum Yield",'') then "Maximum-Yield"
else [Financial Objectives]

endif

[fmvizcaino]

Hi @mem2243 ,

One possibility is to use the contains formula as follows:

contains([Financial Objectives],"Long Term Growth").

Best,

Fernando Vizcaino

[mem2243]

I tried that. It also only works if it's the only name in the column

[fmvizcaino · Accepted solution]

Hi @mem2243 ,

Sorry, now I understood what you really need.

For that, you can use replace( [Field1],"Long Term Growth","Long-Term-Growth") directly for each case you want to replace.

Best,

Fernando Vizcaino

[danilang · Accepted solution]

Hi @mem2243 

If you modify your formula to 

if REGEX_Match( [Financial Objectives],".*?Long Term Growth.*",'') then "Long-Term-Growth"
elseif REGEX_Match( [Financial Objectives],".*?Maximum Yield.*",'') then "Maximum-Yield"
else [Financial Objectives]

endif

You'll match any thing that contains the target.  The .*? matches anything before but not including the target and .* matches anything after.  Here I created a new column to show the concept, but it will work the same if you use Financial Objectives as the output column

Dan

[mem2243]

thanks, what if I also want to replace Maximum yield?

[mem2243]

I still want to bring back the other words in the column

[fmvizcaino]

You can insert a second formula in your formula tool or you can nest one inside another;

replace([Financial Objectives],"Maximum Yield",'"Maximum-Yield")