Filter values falling in range

[srk0609]

I want to extract values that are falling in below range

13700-139ZZ
J0000-JZZZZ
HB000-HBZZZ
HC000-HC000
G0000-GZZZZ

where Z indicates value from 0 to 9 and A to Z

so for example 13701,1370A,137BB,139A6 would all be considered in range. Can anyone help me with formula to filter these values from dataset? Thank you

[binuacs]

@srk0609 one way of doing this

[srk0609]

This won't work cos it pulls Id starting with 136 example it will pull record 13600, 13699 but I want only values in 13700 to 139ZZ to get selected not starting 136 or 135. Can you please update formula

[srk0609]

I modified it to get solution. Thank you for your help.

[AGilbert]

@srk0609what was your solution? 

You can convert the 5-character string on a base-36 numeric system. Similar to a hexadecimal base-16 system. I'm testing on my end; so, I'd appreciate any feedback you have. 

To implement, you would split the range string on the '-' character and convert both numbers with the macro as an upper and lower limit. Then convert each of your target values to have comparable values for a >= AND <= filter expression. 

Edit: I guess this isn't really a hash function, so I removed that language.

[srk0609]

what changes should I make to the workflow to exclude 13700 - 13707. The field is a V_WString format

[srk0609]

what are the changes I should make to the workflow to exclude 13700 - 13707. The field is a V_WString format

[Qiu]

@srk0609 
Just go ahead and filter😁 Suprise! Alteryx has taken this into consideration.