Semi Random Order Number Generator

[Eduard_Grubman]

Hi All,

I’m creating a mock-up for a client that could heavily benefit from the use of Air Table and they need to be able to mask their order numbers to prevent vendors from gathering information from where their orders originate.

I’ve done this in the past with Google sheets and I know that it I’ve been able to mask the order number but I’m not able to convert the formula correctly from Google Sheets to AirTable.

See example below.

> IF(LEN({Purchase Order #})<7,CONCATENATE("33",LEFT({Purchase Order #},3)*4,RIGHT({Purchase Order #},3)*7),CONCATENATE("33",RIGHT({Purchase Order #},6)))

A Sample of what the result should look like is:

Modified | Original
334641911 | 116273
33697867 | 113-1303417-4697867

The two fields that are being used are Formula and Single Line Text, please note that the false if condition does work.

Hopefully I can get some guidance on this.

Thank you,

Ed

[Eduard_Grubman]

In case anybody runs into this in the future.

The issue with this is that unlike excel, the left and right functions are strictly of the string type, therefore numerical operators don’t work. The way around this is to cast (convert) the values using the VALUE function.

See my Solution below.

IF(LEN({Purchase Order #})>7,CONCATENATE("33",RIGHT({Purchase Order #},6)),CONCATENATE(33,VALUE(left({Purchase Order #},3))*4,VALUE(right({Purchase Order #},3))*7))

Cheers