Function to return a number of fixed length

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tryfanman@googlemail.com - 07 Feb 2007 10:30 GMT

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

Reply to this Message

Roger Govier - 07 Feb 2007 10:36 GMT

Hi

One way
=LEFT(REPT("0",10),4-LEN(A1))&A1

Signature

Regards

Roger Govier

> Hello,
>
[quoted text clipped - 7 lines]
>
> Kind regards

Reply to this Message

Ron Rosenfeld - 07 Feb 2007 11:32 GMT

>Hello,
>
[quoted text clipped - 7 lines]
>
>Kind regards

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

Reply to this Message

tryfanman@googlemail.com - 07 Feb 2007 16:49 GMT

> >Hello,
>
[quoted text clipped - 22 lines]
> Is this what you want?
> --ron

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.

Reply to this Message

Ron Rosenfeld - 07 Feb 2007 18:37 GMT

>> >Hello,
>>
[quoted text clipped - 34 lines]
>
>Thanks you your help. It is appreciated.

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

Reply to this Message