Is there formula to convert value to text and if necessary add leading zero?

There are several ways of doing what you ask. Here are two.

The formula below the table has been entered in cell B2, then filled down column B to Row 6. Note what happens with numbers (column A) with more than six digits.

…more v

There are several ways of doing what you ask. Here are two.

The formula below the table has been entered in cell B2, then filled down column B to Row 6. Note what happens with numbers (column A) with more than six digits.

This formula has been filled right to D2, then both copies were filled down to row 6.

Essentially the same as the original formula, but here the LENgth of the text string containing 6 or 8 zeros is calculated by the LEN function instead of by a number written into the formula. This allows changing the number of characters to be included in the text string representing the number without having to make any changes to the formula.

In column C, where there are 6 zeros in the top cell, this makes no difference to the results. Rows 4 and 5 of column C contain the same results, despite the difference in the original values in column A.

In Column D, though, where D1 contains a string of eight zeroes, rows 4 and five contain eight character strings expressing the same values as those in column A.

Regards,

Barry

PS: When entering the 'numbers' as text in row 1, take advantage of this short cut:

Start the typing in each cell with an apostrophe. This tells Numbers that what follows is to be treated as text, not as a number. The ' will not be visible in the cell.

Regards,

B

And another way if using a formula:

=REPT(0,6−LEN(A2))&A2

Use ; instead of , in the formula if your region uses , as the decimal separator.

SG

Using the numeral system format as suggested by Yellowbox, you can pad the values in column A without needing a column B. If you need them to be text, the formula =A&"" in column B will convert them to text or you can use the values from column A directly in text formulas and it will use the padded numbers.

Or, a formula that replaces the one from Excel would be

=RIGHT("000000"&A,MAX(LEN(A),6))

Hi SunnyInToronto,

You can change the cell Data Format to Numeral System, with Base 10 and Places 6 to display 89 as 000089.

Not text, but a number that can be used in calculations.

Regards,

Ian.