Finding alphanumeric words in a cell and setting the value to 1

[Abhii2658]

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. 

[atcodedog05]

Hi @Abhii2658 

Can you provide this data in excel.

[atcodedog05]

Hi @Abhii2658 

You can use formula like this

IF REGEX_Match([Vendor], "\d+") THEN 0 ELSE 1 ENDIF

Workflow:

Hope this helps : )

[Jean-Balteryx]

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.

[HomesickSurfer]

Hi @atcodedog05 

I like it!

[messi007]

@Abhii2658,

@atcodedog05 nailed it 🙂 below Another way how you can do it as well :

Attached the workflow.

Hope this helps !

Regards

[Abhii2658]

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 

[atcodedog05 · Accepted solution]

Hi @Abhii2658 

Here is how you can do it.

Workflow:

Hope this helps : )

[Maskell_Rascal]

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

[Jean-Balteryx]

@Abhii2658 that"s how I treated it but @atcodedog05's solution uses one less tool !