Function to return a number of fixed length

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

0
tryfanman (2)
2/7/2007 10:30:29 AM
excel 39879 articles. 2 followers. Follow

4 Replies
515 Views

Similar Articles

[PageSpeed] 58

Hi

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

-- 
Regards

Roger Govier


<tryfanman@googlemail.com> wrote in message 
news:1170844229.353427.149590@p10g2000cwp.googlegroups.com...
> 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
> 


0
roger5293 (1125)
2/7/2007 10:36:16 AM
On 7 Feb 2007 02:30:29 -0800, tryfanman@googlemail.com wrote:

>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

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
0
ronrosenfeld (3122)
2/7/2007 11:32:43 AM
On Feb 7, 11:32 am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On 7 Feb 2007 02:30:29 -0800, tryfan...@googlemail.com wrote:
>
> >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
>
> 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


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.

0
tryfanman (2)
2/7/2007 4:49:06 PM
On 7 Feb 2007 08:49:06 -0800, tryfanman@googlemail.com wrote:

>On Feb 7, 11:32 am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> On 7 Feb 2007 02:30:29 -0800, tryfan...@googlemail.com wrote:
>>
>> >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
>>
>> 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
>
>
>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.


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
0
ronrosenfeld (3122)
2/7/2007 6:37:22 PM
Reply:

Similar Artilces:

Determining length of trend before significant retrace
In range of data I need to find jthe largest numerical movement in one direction without (x) retrace. (x in this case being a specified whole number) Please help with a formula or direct me to where I could find this information. Using Excel 2007 For example: in range of numerical data, the largest directional integer movement was 300 before the data moved 25 in reverse. Example 2: The largest directional integer movement was 224 before the data moved 30 in reverse. Assuming you have a column of numerical integer data in column B, In C2, enter =ABS(B2-B1) to calculate the magnitude ...

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

Obtaining a hot fix for Event 9582
Greetings! I have an Exchange 2000 Server standard edition (SP3 + latest roll-up) running on Windows 2K (SP4 + latest updates). The server has 2 GB of RAM and I do NOT have the /3GB switch in the boot.ini file. Yesterday this server began generating memory fragmentation errors. The event log item is below: "Event Type: Warning Event Source: MSExchangeIS Event Category: Performance Event ID: 9582 Description: The virtual memory necessary to run your Exchange server is fragmented in such a way that performance may be affected. It is highly recommended that you restart all Exchange s...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Function doesn't run
In my spreadsheet, I have the following function =VLookup(K16, zips, 2) However, instead of returning a result, the function remains in the cell. How do I fix this problem? Format the cell as General and re-enter the formula (F2, ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Justin" <jmeyer@incrementaladvantage.com> wrote in message news:1165596899.059148.31580@80g2000cwy.googlegroups.com... | In my spreadsheet, I have the following function | =VLookup(K16, zips, 2) | However, instead of returning a result, the function remains in the | cell. How do I fix th...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Today Function
how is this function called in the code? I want to use in in an update query that is coded to a button. thanks Hey Dave, I hope I'm understanding what your asking for but I think this is what you are looking for: Today() HTH, Shane Dave wrote: >how is this function called in the code? >I want to use in in an update query that is coded to a button. > >thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 Well I think it is either Today() or Date() not sure which and not sure how to use it in the code. I ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

how can I asign printscreen to a function key or key combination?
My key board has no printscreen key. What can I do? That seems unlikely, but it is hardly a Word issue. The PrtScn button is usually a dual function button somewhere on the top row of your keyboard. If it is not then you need to ask the keyboard manufacturer. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

UDF that returns a Hyperlink
Is it possible in Excel 2007 using VBA to create a UDF that returns a hyperlink? Ideally I want a hyperlink where the display name is different to the linked URL. Any ideas? TIA First, if you mean you want the tooltip to show a different address, then I don't think that it's possible. Second, if you meant you want to see something in the cell that isn't the address of the link, then... There's an =hyperlink() worksheet function that may do exactly what you want: =hyperlink("http://www.microsoft.com","Click me!") Third, If you wan...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...