3

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"

2 Answers 2

2

You can use TEXT to set the amount of digits:

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

enter image description here

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.