Help

Re: Need Help Creating IF Statement

419 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Niko_Gomez
4 - Data Explorer
4 - Data Explorer

Hello!

I’m new on airtable and would love some help creating an IF formula.

Context: I’m trying to create a unique vendor code for SKUs. Ive added an autogenerate number field called “Vendor Number” and added a “vendor code” field where I would like to create the IF formula. I want to to add “00” at the beginning of the vendor number if the vendor number is less than 10, add “0” if the vendor number is less than 100 and show only the “vendor number” if it is greater than 100.

Example:

Vendor Number: 7
Vendor Code: 007

Vendor Number: 19
Vendor Code: 019

Vendor Number: 122
Vendor Code: 122

Any help would be greatly appreciated!

Thanks

1 Reply 1

Hi @Niko_Gomez - you can do this with an IF statement (if length = 1, then prefix with 00 etc), but there’s a neater way:

REPT('0', 3-LEN({Vendor Number} & '')) & {Vendor Number}

Just to break this down:

  • This uses the REPT function to repeat a string a number of times
  • We use 3 - length of the vendor number to work out how many times to repeat
  • We then join the number of prefixed zeros with the original vendor number to get the result we want:

Screenshot 2021-06-02 at 05.35.00

=======================

Want to learn Airtable Scripting? 1 day bootcamp coming soon!

=======================