Defined number of characters

[Carrie]

I have a spreadsheet that has numerical values in cells of different lengths,
ie xxx, xxxxxx, etc. I need to make all the values 6 characters long by
adding or removing zeros at the beginning and therefore not altering the
value. I then want it to appear as text rather than number. Can anyone help

[David Billigmeier]

If your value was in A1:

=TEXT(A1,"000000")


--
Regards,
Dave


"Carrie" wrote:

> I have a spreadsheet that has numerical values in cells of different lengths,
> ie xxx, xxxxxx, etc. I need to make all the values 6 characters long by
> adding or removing zeros at the beginning and therefore not altering the
> value. I then want it to appear as text rather than number. Can anyone help

[pinmaster]

Try someting like:

=IF(LEN(A1)<=6,TEXT(A1,"000000"),TEXT(RIGHT(A1,6),"000000"))

HTH
JG

"Carrie" wrote:

> I have a spreadsheet that has numerical values in cells of different lengths,
> ie xxx, xxxxxx, etc. I need to make all the values 6 characters long by
> adding or removing zeros at the beginning and therefore not altering the
> value. I then want it to appear as text rather than number. Can anyone help

[PCLIVE]

One way:
=REPT(0,6-LEN(A1)) & A1

HTH,
Paul

"Carrie" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet that has numerical values in cells of different
>lengths,
> ie xxx, xxxxxx, etc. I need to make all the values 6 characters long by
> adding or removing zeros at the beginning and therefore not altering the
> value. I then want it to appear as text rather than number. Can anyone
> help