IF Contains alphanumeric, numeric
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Datasets
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No worries @fabs - thanks for the clear explanation of the rules! How does this look? Amended workflow attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
this helped a lot! thank you so much!!!!!!!!!!!!!!!!!
