adding a zero in front of number

how do you add a zero in front of other numbers, I am using item numbers and 
most start with zero, just shows whole numbers when I enter.  example 095421
when I enter shows 95421. help.
0
Pinky (5)
4/7/2005 7:03:03 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
525 Views

Similar Articles

[PageSpeed] 54

Hi

When the number must remain numeric data, then format the cell as Custom
"00000" (the number of 0's determines to which length is the entry padded).

When you want the number to be converted to string, then use the formula (in
my example the original number resides in cell A1)
=TEXT(A1,"00000")
(again, the number of 0's in format string determines the length of padding)


Arvi Laanemets


"Pinky" <Pinky@discussions.microsoft.com> wrote in message
news:AB74F654-B323-475F-8FA8-FC317FF3811D@microsoft.com...
> how do you add a zero in front of other numbers, I am using item numbers
and
> most start with zero, just shows whole numbers when I enter.  example
095421
> when I enter shows 95421. help.


0
4/7/2005 7:21:40 PM
>-----Original Message-----
>how do you add a zero in front of other numbers, I am 
using item numbers and 
>most start with zero, just shows whole numbers when I 
enter.  example 095421
>when I enter shows 95421. help.
>.
>

You will have to custom format the cells in question 
as "00" or "000" or "0000" etc depending on number of 
leading Zeros you wish to put. The 1 will show as 01 or 
001 or 0001 etc resp.
Select the cells in question. Go to Format-Cells-Number-
Custom. Then type 00 or 000 or 0000 etc in the Type: box 
to the right side. 
Hope this helps you. You can reach me at 
sunilsgawde@hotmail.com for more assistance.

0
anonymous (74722)
4/7/2005 7:27:24 PM
"Pinky" <Pinky@discussions.microsoft.com> wrote in message
news:AB74F654-B323-475F-8FA8-FC317FF3811D@microsoft.com...
> how do you add a zero in front of other numbers, I am using item numbers
and
> most start with zero, just shows whole numbers when I enter.  example
095421
> when I enter shows 95421. help.

Enter the number as '095421 with a leading apostrophe.

/Fredrik


0
4/7/2005 8:05:23 PM
Reply:

Similar Artilces:

How to sort number follow cell accordingly
Hi, everyone! I need to create macro that can sort numbers as below: Column : Before A B C D E F 1 2 3 4 5 6 3 4 5 1 3 4 5 3 5 6 After A B C D E F 1 2 3 4 5 6 3 4 5 1 3 4 5 3 5 6 which mean same number follow the same column but not necessary follow column A,B,C..... Pls help with vba code.Thanks GeniusIdeas, You have to evaluate the assumptions in the program (i.e. read the comments and evaluate the code). Basically, Integers are assumed for the sort, and the Integers start at 1 and each...

Receivables Override document number Setup option
There is a setup option in payables that allows the choice and also in SOP for the various document types but it seems not in receivables. It would be most useful to have this option available in Receivables setup also. This has been raised as a particular concern for cash receipt transactions. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft ...

Rounding Value Up to Whole Number
I have a report footer that I'm adding values from the detail section. Many of my values that I'm adding are .333. Once I sum the value in the footer, I end up with not a whole number, for example 9.999. Is there a property I can set to bring the value to the next whole number. Thanks for your help Try =- Int( - [MyField]) (note there is a - before the Int and before the my field) Substitute the name of your own text box) Evi int(number*100 + 0.5) / 100 "PowellGirlTN" <PowellGirlTN@discussions.microsoft.com> wrote in message news:CBDE76F7-8837-4223-BF5C-E...

Adding an image to an Image control
I would like to create a worksheet where I can store information on number of items. Each row of the worksheet would store data on 1 item only, and the last cell of data would contain a link to an image of the item (C:\Users\Dad\Desktop\test.png). What I can not figure out is how to create the code to display the image in an Image control on my Userform. What I tried was: Image1.Picture = Range("E3").Value where Range("E3") contained C:\Users\Dad\Desktop\test.png. Is this do able? If so could someone point me in the right direction. Image1.Pi...

How to reduce the number of 'clicks' when saving
How can I control the number of clicks to one when saving changes in an Excel spread sheet? I use lots of small sheets which I edit and save and every time I click the save button I get three or more "are you sure" questions all of which I want to answer yes. Finger fatigue!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements If you save a workbook over an existing...

Page numbers don't appear in subreport
Hi all, I have a simple problem with a report. In the page header of the report I list page x of z in the usual fashion. However, the report contains a subreport and if that subreport runs into multiple pages the main report page header doesn't print the page number on those pages. Any ideas to force the main report page header to print on every page even if the only data on the page is from the subreport? Dannasoft wrote: > Hi all, > > I have a simple problem with a report. In the page header of the > report I list page x of z in the usual fashion. Ho...

Adding fields into Workflow Rule
I am trying to create a workflow rule where the assignment of a case is dependant on the Product type. However the Product field does not appear in the Field list for the Case Object. Is there a way to add the field into the picklist? ...

Null to reflect as zero
Hi, I have a query with loads of tables and crosstabs. Its basically a stats report. My problem is that sometimes there are cells that are blank but i dont want that. I'd prefer it to reflect as a zero. how do i do that? Er, im no sql boffen but im guessing you are going to ask for that so here it is. LOL Please dont reply in sql language as i wouldnt know what to do with it :) Ta Whoops... Here we go: SELECT tblTrainingSession.dateStartDate, tblTrainingSession.dateEndDate, suptblCourse.txtCourse, suptblLevel.txtLevelAbbreviation, tblClientContact.txtCom...

Extracting the data according the number of cell (at specific range) #2
I have a minor Excel formula problem. In my example has following data, cell A5 shows "apple", A6 shows "salt", A7 shows "sugar" and A8 shows "fish". On another hand, cell B3 is the "criteria" data showing 3. My question is to form an Excel formula which can extract the data from cell "A1" and then extract the 3rd data, which is "sugar" (that is the result I want). Althought the formula is counting from A1, but the excel formula know the data after "apple" and "salt" is "sugar" (cell fro...

Outllok Rule Creation based on Greater Than Numbers...
Program: Outlook 2003 He Everyone, My boss has requested the creation of a particular type of rule that has me stumped. He receives many quote requests per day but we are only interested in them if they are BELOW a certain quantity, i.e. we would be interested in fulfilling an order for less than 1000 but not more than that. I have tried filtering based on "Specific words in the body" with entries such as ">1000," (hoping Outlook would know to treat the entry as a number above 1000, also tried "1,***," (hoping it would recognize as a number w/wildcards)...

How can i have numbers on both y axis's
I wan to be able to have data on both sides of the column graph. Is that possible In order to show the secondary Y axis, you need to plot at least one series on the secondary axis. It's tricky with column charts, so I would add a series as a line chart series and assign that to the secondary axis. On the Chart menu, go to Source Data, and on the Series tab, click Add. Select some reasonable data for the Y values and click OK. Select this new series, and on the Chart menu, use Chart Type to change it to a line series. Double click the line, and on the Axis tab, select Secondary, the...

Continuous form / sequential numbering
I am running Access 2003 and want to set up something on a continuous form in a multi-user environment simply for the user (aesthetic) when entering data. User must enter countermeasures on a subform, he can enter a maximum of 5 and I have a code running on the subform: Private Sub Form_Current() LimitRecords Me, 5 ' allow at most 5 records End Sub What I want to do, is for each record added (1 to 5) they are numbered on the form. I did some hunting and found: Private Sub Form_BeforeInsert(Cancel As Integer) Dim x As Integer With Me.RecordsetClone ...

Trouble Adding Data to PivotChart
I have a puzzling situation, it's probably a simple setting that can be change. I'm running Windows 2000 and Access 2002. I can create and modify Pivotchart but when I share it with other people who are running the same OS and Access version, they were not able to modify the chart. They can't even create their own PivotChart view in a database that they create. Have anyone come across this problem? One more thing, while researching this issue I found this topic in Help: Prevent users from making changes in PivotChart view. The steps it goes through tells me to click on p...

numbers are converted into dates
i type numbers into a cell and they are automatically converted into dates when i hit enter, or i hit enter and it gives me a larger number that i didn't type in. i canceled auto format and tried format, cell, numbers but no luck. thanks! Hi if this is only happening in one workbook try this: with your workbook open, open up a new workbook, in the new workbook, select any cell and copy it now switch to your workbook - select the misbehaving cells and choose edit / paste special - ADD click the OK button and now try and let us know how you went. Cheers JulieD "vb" <vb@...

Add number of used rows to footer?
Is it possible to add the number of used rows to a spreadsheet's footer? I have a basic list in Excel 2000. the number of rows in the list changes weekly, and I would like to find a simple way to: 1 - Count the number of rows in the current list (minus the header row) 2 - Display this number in the footer of the printed spreadsheet. If anyone can help with either of these, I'd appreciate it! Thanks, E If there are no embedded blank cells in column A, you can get a count of the filled rows (minus the header) with the formula =COUNTA(A:A) - 1 To put that number in a footer, you nee...

Adding within a cell
I'm adding to the same cell continouslythroughout my worksheet. Can I keep entering data within a cell and have it add on its own without having to add in my head and change the cell each time?? Take a look at http://www.mcgimpsey.com/excel/accumulator.html In article <4111F0B3-FD66-41DB-ADDA-89BD4C022139@microsoft.com>, "Rosa" <Comoestanohay@aol.com> wrote: > I'm adding to the same cell continouslythroughout my worksheet. Can I keep > entering data within a cell and have it add on its own without having to add > in my head and change the ce...

Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based on the metadata selected from dropdowns in columns H, I & J, the spreadsheet builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined number; nnnnn= sequential number). I'm using the following formulas: Col B =VLOOKUP(H577,FUNCTIONLU,2) Col C =VLOOKUP(I577,CATEGORYLU,2) Col D =VLOOKUP(J577,TYPELU,2) Col E =IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...

adding text in front of
Hi, I would like to insert a common beginning in front of all my lines of text on a certain collum . How do I do that , my text being different on each lines example from Bob to - Bob -- Our website : http://timmins22.adventistchurchconnect.org/index.php Our Bible School : http://www.discoveronline.org/schools/647 try Sub adddash() mc=activecell.column lr=cells(rows.count,mc).end(xlup).row For Each c In range(cells(2,mc),cells(lr,mc)) If Len(c) > 1 Then c.Value = "- " & c Next c End Sub -- Don Guillett SalesAid Software dguillett1@austi...

Is there any way to make the format (General, Number, Text) of the cells visible?
Is there any way to make the format (General, Number, Text) of the cells visible? I always have to rightclick > Format to check it. Bart Ex2003 Hi Bart There's a way to create a formula to display the number format using an XLM4 macro function: 1. Go Insert>Name>Define and name the formula you are about to create something like NumFmt (you have to do it this 'indirect' way when using XLM4 macros) and in the refers to box type: =GET.CELL(7,OFFSET(INDIRECT("RC",FALSE),0,-1)) and click OK. 2. Now this formula can be used in any worksheets in this workbook a...

clicking on a cell to have it add a number
Is there a way to click on a cell and have it put in a "1" the first time you click, and then if you click it again have it put in a "2" and so on? We do surveys in houses and need to count for example the number of chairs. So, if we have a cell that says "chairs" and next to it the place to put the number of chairs, we want to be able to click on the blank cell and have it include the number of chairs, one for each click. Is this possible and how would you do it? Thanks for your help. Check out the use of a SPINNER. You can go up or down, just in case y...

Dynamically adding menu items and their OnClick events
How do I add items to a menu while simultanesously adding an OnClick event for each. I have an 2Xn array of text items (labels) and matching URLs.e.g. "Google", www.google.com "Yahoo", www.yahoo.com I want the first column to be the menu label and the second column is where the browser goes when the menu is clicked. OK, so the first part is easy. AdditionalToolStripMenu.DropDownItems.Add(strResearchLinksArray(i, 1)) How do I go to strResearchLinksArray(i, 0) when that item is clicked. I am vb.net 2005. -- Thanx & Regards, Anil Gupte Am 02...

How to zero out returned quantities in Inventory (GP 9.0)
We have some erroneous returned quantities that should all be deleted. Is there an easy way to do this without adding them back into the qty on hand? We're on MS SQL and are familiar with Query Analyzer, but I don't know which tables and which fields to look for. In the past, I used Resource Descriptions to try to list all the tables but CheckLinks brought the values back in. Thanks for any assistance. ...

Printed copies numbering
I have a single sheet ( single Page ) and i want to print multiple copies of it ... Is thier an option in excel that auto numbers those printed copies ? i.e if i need 10 copies I want it to be auto numbered 1..2..3..4.....10 Please help NoChance Code from Ron de Bruin's site. http://www.rondebruin.nl/print.htm#number Sub PrintCopies_ActiveSheet() Dim CopiesCount As Long Dim CopieNumber As Long CopiesCount = Application.InputBox("How many Copies do you want", Type:=1) For CopieNumber = 1 To CopiesCount With ActiveSheet 'number in c...

How to restrict disabled AD user account receive e-mail
We have Exch 2007 SP1 & AD W2K3 SP1. When we disable an user in AD, the user still continues to receive e-mail. There is an option in E2K7 to disable mailbox. But, is there a way to restrict disabled AD user account to receive e-mail. On Tue, 6 Apr 2010 20:26:01 -0700, sekhar <sekhar@discussions.microsoft.com> wrote: >We have Exch 2007 SP1 & AD W2K3 SP1. When we disable an user in AD, the user >still continues to receive e-mail. There is an option in E2K7 to disable >mailbox. But, is there a way to restrict disabled AD user account to receive >e-mail...

how do i multiply entire columns of numbers by a %
put the percent in a cell somewhere>copy>select the range desired>paste special>multiply -- Don Guillett SalesAid Software donaldb@281.com "stgaw53" <stgaw53@discussions.microsoft.com> wrote in message news:F7489B00-8D48-46BF-B208-080A4FB4BE4B@microsoft.com... > ...