Transform String if substring is present

[romain_romain]

Hello, I would like to transform a string if a substring is present inside it.

For example I would like to transform each string containing the substring "Computer" into "Computer Science" and keep the string that don't contain Computer the same as before. The first column is what I have and the second column is what I would like :

Column 1	Column 2
Computer Network	Computer Science
Agriculture	Agriculture
Computer Software	Computer Science
Accounting	Accounting

I tried to do that with a formula by using RegexReplace, my exact formula is :
if REGEX_Match([Column 1], '.*Computer*.') then "Computer Science" else [Column 1] endif

But it is not working.
Anyone has any tips on how to do this?

Thanks in advance.

[randreag]

Hi @romain_romain 

For this you dont need regex a simple if would do it.

if Contains([Column 1], '.*Computer*.') then "Computer Science" else [Column 1] endif

I hope it helps

[Luke_C]

Hi @romain_romain 

Definitely agree with @randreag's solution. For your attempt at regex, you are close. The below should work. I only tweaked the *. to .* after Computer. 

if REGEX_Match([Column 1], '.*Computer.*') then "Computer Science" else [Column 1] endif

A non-regex formula would look very similar:

if Contains([Column 1], 'Computer') then "Computer Science" else [Column 1] endif

[apathetichell]

Agree with @randreag and @Luke_C  but if you want to do a one line regex - you could do worse than:

REGEX_Replace([Column 1],"(Computer)(.*)","$1 Science")

[Luke_C]

@apathetichell Fancy! Definitely need to brush up more on this

[apathetichell]

@Luke_C  TBH I wasn't 100% sure I'd get the syntax down so I needed the practice too...

[vizAlter]

Hi @romain_romain — You can also try a "Find Replace" tool:

Hope this helps!