Displaying the zero value in a cell

Is it possible to display a zero at the front of a series of numbers in a
cell?

Eg. "0123" is displayed as "123". How to display as original "0123"?

I've got the 'zero value' checked in Tools - Options - View. That is only
for a zero value in a cell without any other numbers in that cell.

Thanks in advance!!


0
bswong19 (6)
6/21/2005 12:28:57 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
417 Views

Similar Articles

[PageSpeed] 58

Sure. Just type it with an apostrophe in front, or format the cells. For a
good example, choose Format-Cells, Number tab. Then choose
Special-->Zipcode. It shows "00000". So if your numbers are 10-digit account
numbers, put "0000000000"
Hope it helps!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bennos" <bswong19@yahoo.com.au> wrote in message
news:dbJte.24574$F7.12102@news-server.bigpond.net.au...
> Is it possible to display a zero at the front of a series of numbers in a
> cell?
>
> Eg. "0123" is displayed as "123". How to display as original "0123"?
>
> I've got the 'zero value' checked in Tools - Options - View. That is only
> for a zero value in a cell without any other numbers in that cell.
>
> Thanks in advance!!
>
>


0
ng1 (1444)
6/21/2005 12:48:04 AM
Silly question - Which version of Excel have you got? I have 2003 Small
Business Edition, it doesn't give the option for Special -> Zipcode in the
Format-Cells -> Number Tab options.


"Anne Troy" <ng@officearticles.com> wrote in message
news:e2959$42b763bb$466eb880$8806@allthenewsgroups.com...
> Sure. Just type it with an apostrophe in front, or format the cells. For a
> good example, choose Format-Cells, Number tab. Then choose
> Special-->Zipcode. It shows "00000". So if your numbers are 10-digit
account
> numbers, put "0000000000"
> Hope it helps!
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "Bennos" <bswong19@yahoo.com.au> wrote in message
> news:dbJte.24574$F7.12102@news-server.bigpond.net.au...
> > Is it possible to display a zero at the front of a series of numbers in
a
> > cell?
> >
> > Eg. "0123" is displayed as "123". How to display as original "0123"?
> >
> > I've got the 'zero value' checked in Tools - Options - View. That is
only
> > for a zero value in a cell without any other numbers in that cell.
> >
> > Thanks in advance!!
> >
> >
>
>


0
bswong19 (6)
6/21/2005 1:24:38 AM
Bennos,

One way is to format the cell(s) (row, column, whatever) as text (Format - 
Cells - Number tab - Text).  You must reenter the numbers.  Another is to 
precede each with an apostrophe (') as you type it, which also makes it 
text.  If these will be used mathematically as numbers in formulas, there 
may be trouble -- they're text, not numbers.  To keep them as numbers, if 
all the numbers in the cells are to have e.g.: four digits (0001, 0012, 
0123, 1234), then you could format the column for four digits (Format - 
Cells - Number tab - Custom - 0000).  That method will give you numbers, not 
text, which you can use in  formulas.
-- 
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Bennos" <bswong19@yahoo.com.au> wrote in message 
news:dbJte.24574$F7.12102@news-server.bigpond.net.au...
> Is it possible to display a zero at the front of a series of numbers in a
> cell?
>
> Eg. "0123" is displayed as "123". How to display as original "0123"?
>
> I've got the 'zero value' checked in Tools - Options - View. That is only
> for a zero value in a cell without any other numbers in that cell.
>
> Thanks in advance!!
>
> 


0
nothanks4548 (968)
6/21/2005 1:31:41 AM
Reply:

Similar Artilces:

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

Can't get the proper display of a field in my report.
I have 2 tables, both using autonumbers for their primary key. The first table is for contacts (i.e. last name, first name, etc.). The second table is for businesses (business name, etc.) I have a field in my contacts table that has a number format so it can be used as a foreign key for the business table. I then set up the relationship between them & enforced referential integrity. When I run a query, I see the name of the business (after setting up a combo box) - no problem. When I run a report based on that query, a number is displayed (not the business name). Suggestions, pleas...

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009601098-9 2004 000801098-...

Results from blank linked cells
I am linking cells from different worksheets in the same workbook, using the copy/paste/link cell method. How can I get a blank space (as opposed to the zero I am presently getting), in the destination, if the source cell is blank. I am linking a input sheet to several forms that must be sent out, but I don't want a form that will have a number of zeros in it. =if(sheet1!a1="","",sheet1!a1) If the linked cell looks empty, show empty, else show the value. Mr. Anolog wrote: > > I am linking cells from different worksheets in the same workbook, using the &g...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Displaying a dataset or datatable in Excel
There's something I think ought to be easy, but I can't find a way to do it. Either there's something wrong with the way I'm searching, or there's a reason it can't be done easily. What I want is to create an excel addin that, when the user opens a spreadsheet , it goes out and fetches some data from a database, and displays it in a table. Pretty straightforward. Here's some code: void Application_WorkbookOpen(Microsoft.Office.Interop.Excel.Workbook wb) {//I've also attached this to other events. The "open" event isn't...

Comments don't display properly
I'm working on a spreadsheet in Excel 2003. I've been adding comments in the normal way (Insert Comment / Edit Comment). In the Tools / Options / View I've got "Comments indicator only" selected. I see the little red triangle. Till recently, when I hovered the mouse over a cell with a column indicator, the comment popped up. However, something has happened now so that when I hover the mouse over the cell, I see the line leading to the comment, but the comment text box seems to be collapsed. In some cases, the comment box consists of simply a line, with the com...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

worksheet labels based on cell results
How can I build a macro to use the contents of several cells in a column to label a corresponding number of worksheets with their contents. Ideally this would also build links to the tabs so that a user could click on a specific cell (in that column) and be redirected to the corresponding worksheet Thanks, Mitch Hi for labeling the tab try something like activesheet.name=activesheet.range("A1").value For the second question try using a Hyperlink (Insert - Hyperlink) -- Regards Frank Kabel Frankfurt, Germany Mitch wrote: > How can I build a macro to use the contents of ...

how to compare 2 values in a report (Invoice Total vs Payments)
while running a report how would I set a message "out of balance" if my invoice amount (Table 1 ) does not equal the value of my total payments (table 2). If the values were the same then no message would be printed. Thank You Create a new command button to check the report before you print it. You will have to enter code for the button along the lines of: If [Invoice Total] <> [Payments] Then MsgBox "This account is out of balance" Endif "Rita" wrote: > while running a report how would I set a message "out of balance" if > my invo...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page
please help Hi maybe: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "confused" <confused@discussions.microsoft.com> schrieb im Newsbeitrag news:36180425-96C2-4368-9DE0-3E60741154FF@microsoft.com... > please help Just click on a cell and nothing else? Or perhaps you mean click on a cell then as you move the cursor around other cells are selected like the cursor is stuck? If the former, you must have some event code which selects the cells or you are clicking on the gray box at intersect of rows and columns(top left above ro...

Help with cells auto formatting
Hi, I have posted a similar question before however I never really got this sorted so sorry for repeating myself. Basically I use two spreadsheets daily at work all with various information on and various formats in each Column. My problem is when I close the spreadsheet and reopen it the cells that are formatted as 'general' or 'number' turn into Euro currencies. Does anyone know why this happens or how I can stop it? The spreadsheets aren't stored locally they are stored on a serve that only myself and my boss can get onto and we both have the same p...

Displaying Sheet Tabs Names in Cells
Can anyone tell me if there is a way for me to display the Name of a Sheet Tab in a cell. Take for example I have 4 worksheets, labelled Shawn, Kevin, Mary, & Data. In data, I want it to show the name of the worksheets. So in Cell A4 I would like it to say Shawn, then in B4 I can put Shawn's data in. Is there a function or a formula that allows me to do this. Thanks for any help. Hi Maybe this UDF will do for you. ------ Public Function TabI(TabIndex As Integer) As String Application.Volatile TabI = Sheets(TabIndex).Name End Function ------ Insert the function in...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...

mapping keystroke to a cell
I would like to map a keystroke to a cell and have it add up the number of times the keystroke has been applied. For instance if I touched the A key 3 times it would give me the value of 3 in one cell. If I touched the B key 2 times it would give me the value of 2 in another cell. I'm thinking of using this in a laboratory situation. Where I could count the different types of blood cells under a microscope using the keyboard. Once I reached a total of a 100 diffent types of cells it would play a .wav file that would alert me that the count of 100 had been reached. Using a differ...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...

conditional formatting not working in every cell
I have a worksheet that has conditional formatting set for an grade that is below 95 to turn red. The total worksheet has 160 entries and the formatting works on most of the cells except 4 vells show a grade of 95 and its in red. There are several other cells that are at 95 and are not red. I did a paste special across the worksheet, I have also tried to change the cell itself and I still get the same problem. Any way to fix this? Is it possible that your CF refers to the cell above or below the actua cell? i.e conditional formatting in B2 looks in B1 or B3 Something to look into. Re...

Apostrophes in Blank Cells
I am importing Excel data that has what seems to be blank cells but on closer inspection I can see an apostrphe in the formula bar. That's not a problem because I have written a macro to identify apostrophes and clear the contents of these cells. The mystery is that when the same data is imported on a friend's computer with the same version of Excel the apostrophe does not appear in his formula bar (although they are still non-blank cells) and so my macro will not work on his computer since it doesn't see the apostrophes. Although I can write a different macro in his case, ...

deleting duplicate value in columns
What is the best method to delete rows containing dulicate (same number) numbers in a column (Excel 2000)? The workbook has over 30,000 rows which makes it impractical to sort and then delete the duplicates page by page. Thanks in advance. Hi Jim have a look at http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows Frank Jim Brokenshire wrote: > What is the best method to delete rows containing dulicate (same > number) numbers in a column (Excel 2000)? The workbook has over > 30,000 rows which makes it impractical to sort and then delete the > duplicates page b...

Cond. Formatting depending of value in another sheet
Hi all, I've tried to do conditional formatting whether the cellvalue i another sheet is greater or smaller than the cellvalue I have in th active cell. I've tried to do "formula is" IF(B34<Sheet1!B34)and format, but it won work. Any help is apprecciated!! //Thoma -- Message posted from http://www.ExcelForum.com Hi Try: =B34<Sheet1!B34 -- Andy. "Jonsson >" <<Jonsson.1635tc@excelforum-nospam.com> wrote in message news:Jonsson.1635tc@excelforum-nospam.com... > Hi all, > > I've tried to do conditional formatting whether the ...

Cell Comments #3
First off, I inherited a PC at my new company and the old users name appears in the new comment field, how can I change it to mine. Second, how can I change the default font and appearance of new comments. Thanks, Mark Tools > Options > General and enter your user name -- Gary''s Student "markstro" wrote: > First off, I inherited a PC at my new company and the old users name > appears in the new comment field, how can I change it to mine. > Second, how can I change the default font and appearance of new > comments. > Thanks, Mark > > #1. h...