Matching column data to add a new column for entity name

calvinwong

Hi,

If I have the following data,

Column1

"A2"

"ADS3"

"AFE9_2"

"BAD9_3"

and I'm trying to use a formula tool to add another column to match them to the entity code. I tried to use iff(contain([column 1], "2", 'XYZ', iff(contain([column 1], "3", "ABC", iff(contain([column 1], "9_2", "ERF", "DFS"))). However, given 9_2 also contain a 2, the formula doesn't work. Is there another way to write a formula so that it can return the correct entity name?

Preparation

Accepted answers

MarqueeCrew

Regex_Replace([field1],"(\u+)(\d.*)",'$1|$2')

That will get you something easy to parse. You'll have an easy time joining to your search terms.

Cheers,

Mark

All comments

jasperlch

Hi @calvinwong

There are a couple of things you need to update in your formula:

1. update your iff to iif function

2. update your contain to contains function

3. you need to add an ending bracket for the contains function

4. for the conditional statement, you could simply arrange your nested if conditions. The rule of thumb is always put the more restricted conditional statement (i.e. the condition that is more difficult to matched, e.g '9_2' is more difficult to match than '2') first.

Below should work:

iif(contains([column 1], "9_2"), "ERF", iif(contains([column 1], "2"), 'XYZ', iif(contains([column 1], "3"), "ABC", "DFS")))

MarqueeCrew

Regex_Replace([field1],"(\u+)(\d.*)",'$1|$2')

That will get you something easy to parse. You'll have an easy time joining to your search terms.

Cheers,

Mark

calvinwong

Thanks Mark. That was helpful. Much easier to parse.

I'm trying to learn more and understand about the regex expressions. Is there a good site or place that you recommend?

Thanks,

Calvin

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335