How can I maintain leading zeros in a formula?

Question

I have an array formula as follows:

=ARRAYFORMULA("NUMBER-"&SEQUENCE(33,16,1,1))

The array of numbers from SEQUENCE run from 1 to 528, but I want all the numbers to have matching lengths.

Currently, Google Sheets strips out any leading zeros.

In other words, my formula returns "NUMBER-1" to "NUMBER-528" but I want it to return "NUMBER-001" to "NUMBER-528"

Answer 1

You can use TEXT to set the amount of digits:

=ArrayFormula("NUMBER-"&TEXT(SEQUENCE(33,16,1,1),"000"))

Answer 2

The formula below is the RIGHT approach. Other approaches using the TEXT function such as @Martín's excellent answer have absolutely nothing wrong with them; This approach simply uses the RIGHT function.

=ARRAYFORMULA("NUMBER-"&RIGHT("00"&SEQUENCE(33,16),3))

Explanation

Within the ARRAYFORMULA:

1. A 33x16 array is created using a SEQUENCE of numbers, incrementing by 1, from 1 to 528. The function is simplified to SEQUENCE(33,16) since the optional arguments already default to 1.
2. Strings are created by adding leading zeros "00" onto each number. For example,
   {number:string} ⇾  {1:"001"} ,  {99:"0099"} ,  {511:"00511"}
3. RIGHT is used to return only the last three characters of each string. For example,
   {string:RIGHT(string,3)} ⇾  {"001":"001"} ,  {"0099":"099"} ,  {"00511":"511"}
   
4. Then "NUMBER-" is prepended to each value.

Functions

• RIGHT returns a substring from the end of a specified string.
  RIGHT(string, [number_of_characters])
• SEQUENCE returns an array of sequential numbers.
  SEQUENCE(rows, [columns], [start], [step])
• ARRAYFORMULA enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
  ARRAYFORMULA(array_formula)