Finding alphanumeric words in a cell and setting the value to 1
- 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 70,000 records with various information fields. The requirement is from the Vendor field, if there is an alphanumeric value then set Field A to 1 else to 0
Example
Name Field A
AB1000 Abhijeet Company AB - 1
CD2000 Abhijeet Company AB - 1
EF3000 Abhijeet Company AB - 1
AB1234 Abhijeet Business AB - 1
AB01 Abhijeet Games - 1
IT2658 Abhijeet Company AB - 1
123456 Abhijeet Company AB - 0
45 Abhijeet Company - 0
Hope i am making sense here. Appreciate any advise here.
Solved! Go to Solution.
- Labels:
- Data Investigation
- 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
Hi @Abhii2658
You can use formula like this
IF REGEX_Match([Vendor], "\d+") THEN 0 ELSE 1 ENDIF
Workflow:
Hope this helps : )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Abhii2658 ,
Here is how you can do by extracting your vendor ID then check if it contains only digits. in that case it's false else it's true. I output the result as a Boolean if you need to filter after or anything else but @atcodedog05 solution returns the values you asked for.
- 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
@atcodedog05 nailed it 🙂 below Another way how you can do it as well :
Attached the workflow.
Hope this helps !
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry i dont think i made myself clear, here is the excel
Vendor | Field A |
AB1000 Abhijeet Company AB | 1 |
CD2000 Abhijeet Company AB | 1 |
EF3000 Abhijeet Company AB | 1 |
AB1234 Abhijeet Business AB | 1 |
AB01 Abhijeet Games | 1 |
IT2658 Abhijeet Company AB | 1 |
123456 Abhijeet Company AB | 1 |
45 Abhijeet Company | 0 |
Since the Vendor field has an alphanumeric word "AB1000 " FIELD is 1 and so forth
- 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
Hi @Abhii2658
Here is one more solution that combines both @messi007 and @atcodedog05's solutions. This one should cover you in scenarios where a vendor is listed as just alpha characters, and in scenarios where they are listed as numeric but with any special characters like dashes or periods.
IF REGEX_CountMatches([Vendor], '[a-z]')>0 AND REGEX_CountMatches([Vendor], '[0-9]')>0 THEN 1 ELSE 0 ENDIF
Attached is the workflow.
Cheers!
Phil
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Abhii2658 that"s how I treated it but @atcodedog05's solution uses one less tool !
