TEXT function: format code for padding with zeros?

taraAthan · Newbie Joined: 10 Nov 2007 Posts: 4

I frequently need to convert from numbers (1,2, ...13, ... 123) to text ('001, '002, ...'013, ... '123). Is there a simple way to use the TEXT function (or anything else) to pad with zeros to give a string of fixed length?

h1h · Posted: Sat Nov 10, 2007 10:17 am Post subject:

=TEXT(B15;"000")

will display the number in B15 as Text with leading zeros.
So you might concatenate e.g. =TEXT(B15;"000") & TEXT(C15;"000") & TEXT(D15;"000")
etc.
_________________
OOo 2.3 on Linux

David · Posted: Sat Nov 10, 2007 11:51 am Post subject:

Numbers in column A. In a distant cell [K1 ?], have a string of zeros, prefixed by an apostrophe to force text format. Suppose you wanted to have 5 digit figures:

In B1 put this....

=LEFT($K$1;5-LEN(A1))&A1

Adjust according to the number of digits you need.

Copy down column B.

David.

taraAthan · Newbie Joined: 10 Nov 2007 Posts: 4

Those are both great! Thanks.
I would especially like more information on the format code in
TEXT(B15;'000') - I can't find anything in the documentation about format codes.
Is this written up somewhere?

acknak · Posted: Sat Nov 10, 2007 2:44 pm Post subject:

Help > OO.org Help > Index > "format codes;user-defined number formats"

At the bottom of that page, there's a link to "Custom format codes". or you can use the index under "format codes;numbers"