Function to return a number of fixed length

T

tryfanman

Hello,

I seem to recall (or maybe dreamed of) a function that reterns a
number to a fixed length. For example, if the number in question is
25 and the required length is 4, then the output of the function would
be 0025.

I realise this can be achieved with cell formatting, but I required
this in a function if possible.

Kind regards
 
R

Ron Rosenfeld

Hello,

I seem to recall (or maybe dreamed of) a function that reterns a
number to a fixed length. For example, if the number in question is
25 and the required length is 4, then the output of the function would
be 0025.

I realise this can be achieved with cell formatting, but I required
this in a function if possible.

Kind regards
Click to expand...

It's not clear exactly what you might want.

If you are restricting this to Integers, then something like

=TEXT(Num,REPT("0",Digits))

will return a text string formatted to a specific number of digits.

Of course, if the required length is 4, but the value is greater than 9999,
then the function will return the entire number. It will also round to the
integer, as would formatting.

Is this what you want?
--ron
 
T

tryfanman

It's not clear exactly what you might want.

If you are restricting this to Integers, then something like

=TEXT(Num,REPT("0",Digits))

will return a text string formatted to a specific number of digits.

Of course, if the required length is 4, but the value is greater than 9999,
then the function will return the entire number. It will also round to the
integer, as would formatting.

Is this what you want?
--ron
Click to expand...


Both of these solutions would be suitable. Thanks for taking the time
to reply.

Really I was looking to pad a week number to two places, so week 1
becomes week 01 when returned by the WEEKNUM function.

I also recalled a single function that did just what I asked above,
but it seems I was mistaken. Obviously I'm just trying to confirm I'm
not going crackers which I have failed to do!

Thanks you your help. It is appreciated.
 
R

Ron Rosenfeld

Both of these solutions would be suitable. Thanks for taking the time
to reply.

Really I was looking to pad a week number to two places, so week 1
becomes week 01 when returned by the WEEKNUM function.

I also recalled a single function that did just what I asked above,
but it seems I was mistaken. Obviously I'm just trying to confirm I'm
not going crackers which I have failed to do!

Thanks you your help. It is appreciated.
Click to expand...


If you know exactly what you want, as described above, and you want to use a
function and not formatting, then:

=TEXT(WEEKNUM(TODAY()),"00")


--ron
 
Top