I have the following data in one column. now on the next column, the following shall be met: * if the cell contains Alpha-Numeric then column B should be valued A; * if the cell contains numbers only then column B should be valued B * if the cell starts with BD then column B should be valued C been using formula and contain but I really couldn't get it right, any help is MUCH appreciated! column AABC12341234BD123412345

IF Contains alphanumeric, numeric

fabs

I have the following data in one column. now on the next column, the following shall be met:

if the cell contains Alpha-Numeric then column B should be valued A;
if the cell contains numbers only then column B should be valued B
if the cell starts with BD then column B should be valued C

been using formula and contain but I really couldn't get it right, any help is MUCH appreciated!

column A

ABC1234

1234

BD1234

12345

Datasets

Transformation

Accepted answers

binu_acs

@fabs workflow attached as per your requirement

Checking_27_05.yxmd

All comments

DataNath

Hey @fabs, how does this look? Works for your example but let me know if there are any issues when testing! Workbook attached.

Edit: If there are instances where there may be records with special characters or non-alphanumerics then I've added another rule to catch and null these. If you'd rather they were labelled instead then just replace the null() with 'D' for example.

Regex alphanumeric rules.yxmd

binu_acs

@fabs One way of doing this

Checking_27_05.yxmd

fabs

@DataNath HUGE thanks man!!! 😎 Just had few additional roadblocks tho. There some cells that says "UNKNOWN" and that should be valued to C. and if column A is blank, column B should also be blank.

I tried adding Contains(column A, "UNKNOWN") after Startswith function, but it's still returning "A" value. and for the null values, its giving me "B" value, but it should remain as blank.

DataNath

No worries @fabs - thanks for the clear explanation of the rules! How does this look? Amended workflow attached.

Regex alphanumeric rules.yxmd

binu_acs

@fabs workflow attached as per your requirement

Checking_27_05.yxmd

fabs

this helped a lot! thank you so much!!!!!!!!!!!!!!!!!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703