leading zeros as a cell format

Hi All,

I have an issue with formating cells. I have a permit number that has
leading zeros sometimes, so I must display them when entered. I have
tried 0?-??????? and 0#-####### but no luck. Heres is what my permit
number look
01-2345678 & 02-8765432.


thanks,

Dave
0
7/22/2009 4:21:24 PM
excel 39879 articles. 2 followers. Follow

2 Replies
629 Views

Similar Articles

[PageSpeed] 36

Format as text.

"Dave" <davidstevans@gmail.com> wrote in message 
news:4c0dd9d3-5785-44cd-9d1b-a5f0d2280e28@u16g2000pru.googlegroups.com...
> Hi All,
>
> I have an issue with formating cells. I have a permit number that has
> leading zeros sometimes, so I must display them when entered. I have
> tried 0?-??????? and 0#-####### but no luck. Heres is what my permit
> number look
> 01-2345678 & 02-8765432.
>
>
> thanks,
>
> Dave 


0
rumlas (268)
7/22/2009 4:48:14 PM
Or
00-0000000

Dave wrote:
> 
> Hi All,
> 
> I have an issue with formating cells. I have a permit number that has
> leading zeros sometimes, so I must display them when entered. I have
> tried 0?-??????? and 0#-####### but no luck. Heres is what my permit
> number look
> 01-2345678 & 02-8765432.
> 
> thanks,
> 
> Dave

-- 

Dave Peterson
0
petersod (12004)
7/22/2009 5:39:49 PM
Reply:

Similar Artilces:

Selecting blank cells
Happen that I am trying to set a macro where I have to go to the last row with information, then select "A-last" & "B-last" to the A1:B1, I did this: Range("C65536").Select Selection.End(xlUp).Select But I do not know how to move to select A:B from the last row with information Any help would be appreciated. Fernando Maybe... Dim LastRow as long with ActiveSheet 'use column C to get the lastrow lastrow = .cells(.rows.count,"C").end(xlup).row 'What did you want selected??? .range("a1:a" & lastrow).select ...

Converting Lead Notes and Activies information into Opportunity re
The Lead Activities (phone calls, emails, task, appointments, fax, letter) and the Notes created while working on a Lead do not "convert" into the Opportunity. To get this information, you have to go to Closed Leads or drill down through the Originating Lead link. I've read that this will be in 2.0 but in the meantime, can anyone suggest a simple workaround. I would also like to hear about the best way to do this, simple or note. If I create new fields in the Lead form and map them to new fields in the opportunity form, the activity functionality is reduced to that of ...

Stop spilling to next cell without Word Wrap
I'm trying to stop the text I type into a cell from spilling over into the next cell without using Word Wrap. Searching through past messages I found that someone suggested putting ="" in the next cell to the right to make it look empty. I was wondering if there was a way that would make it so I didn't have to remember to type something in the next cell over. tj You could use event code to enter the space into the adjacent cell. Example code.......... Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in any cell in Col A On Error GoTo en...

DO we need to format the path?
I am writing a macro in which I am passing the name of a file to a function. If I pass just the name of the file everything goes fine, but if i give the ull path, it shows me error. i.e . pass("temp.xls") - > works fine But pass("c:\temp.xls") -> shows error :"Run time error 9, Subscript out of range" DO we need to format the path in some special way or ??? Regards, Jatz -- JAtz_DA_WAY ------------------------------------------------------------------------ JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&...

high light the selected cell
I have a very big spread sheet. Each row goes across 15 cells. How can I have the row outline in a different color to show what row i'm on and once I tab to the cell I need how do I get the colunm outlined in a different color Try Chip Pearson's customizable RowLiner add-in for more positve viewing of selected cell. http://www.cpearson.com/excel/RowLiner.htm Note: does not work on a protected sheet. Gord Dibben MS Excel MVP On Tue, 8 Sep 2009 14:39:25 -0700, Frank <Frank@discussions.microsoft.com> wrote: >I have a very big spread sheet. Each row goes across 15 cells....

Diagram show blank as zero value when based on a formula
When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart will show the blank value "" as zero even if you whant to plot gaps or plot the empty cell interpolated. Yes, I know Excel say empty cell and the cell is not empty when there is a formula. Is there anybody who have found a "work around"? I often have formulas that generate zero in the result but I want to plot the result as if it was empty. I found the reply myself in a question from another user similar to this issue. I will use NA() instead of "" Thank you that works! &q...

Link cells with text not numbers
I'm working with a worksheet of cells with general information (text) that I linked to various cells on worksheets within a workbook. I created the formula below and it works but I'm not sure it is the most accurate way to write the formula. =IF('General Info'!C2>0,'General Info'!C2,"") All linked cells contain text, not numbers, so I'm questioning the >0. Any better alternatives? Although Excel sees a blank cell as zero and you will probably get away with your method, I think you would be safer with. =IF('General Info'...

formating multiple excel .csv files
Hi all I've a folder with about 100 subfolders all containing around 10 .csv files. I need to format all these files using the same formatting (remove some lines, distribute some text to differnt columns etc) Question: Is there any way I can for example set up a macro to do the formatting I need but then automatically make it run through all the csv file in the folder structure ? I look forwart to any responses and appreciate any advice on the topic. Cheers Gerry R Your question is so vague that only outline answer possible which is Yes. I would suggest you have standard names for...

Output report to Word
I have a series of reports that I output to a Word doc in rtf. Is there a way to keep the report format in tact? I.e., can I output the report so that it looks the same as it does in the report viewer? Nope. Not from what I know. One of the frustration of the Export to Word function, the loss of formatting. The solution is to print it as a PDF for distribution or automate the creation of a word document from within access to build the formatting as you require using vba. -- Hope this helps, Daniel Pineault "rs0905" wrote: > I have a series of reports that I o...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

date format problem #7
I have a csv file with date column in the "01/05/1990" format. I could use Notepad and open the file with the correct date displayed ok. When I double click the file and Excel opens it, the formats becomes very strange: some are right justified with "-" as delimiter e.g. "01-05-90" some are left justified with "/" as delimiter e.g. "01/19/1990" I tried to fomat the column to a common date format like "01-Mar-1990" etc but Excel refuses to change. Any help? Thanks. Mike Hi Mike! First check those / dates. I suspect that they are t...

Lead to Product and Lead to Competitor relationships
While browsing MS CRM logical DB Diagrams (http://www.microsoft.com/ dynamics/crm/using/sdk/DBDiagrams.mspx) for Sales Entities, I have found many-to-many relationship between Lead and Competitor entities. In SQL Server I made a diagram with the following tables: LeadBase, ProductBase and CompetitorBase and then I added tables LeadCompetitors, LeadProduct (these tables contain no data in my case). You can see my diagram below. I saw many-to-many relationships between Lead & Product and Lead & Competitor. As far as I understand, these relationships must support functionality for adding ...

VBA Conditional Format
I cannot get why this is giving me the "object doesn't support this property or method" message at the '.Font.ColorIndex = 10' line. What's the deal? I recorded a macro that did the same thing and the code is pretty much the same. Thanks! Dim h As Range Set h = ActiveSheet.Range("D11, D12, D14, D19, D20, D22, K11, K12, K14, K19, K20") With h.FormatConditions .Delete .Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=B11" End With With h .Select .Selection.FormatConditions(1).Font....

how to Sum last 10 values of a dynamic column excluding zero
hi, i have this formula =SUM(OFFSET(P2,MATCH(1E+30,P:P)-1,0,-10,1)) which sums the last ten values in column P, i was hoping someone could amend it slightly for me. As column P is dependent on other entries the entire column has 0 in it which makes this formula ineffective, i can't put an IF formula into column P because then that wrecks other formulas that need column P, so i need to work in a way of excluding zero into the forumla above. Also, the formula does not work until there are ten values in the column, is there any way to make it work and count the values up until ...

Automatically format
I have a workbook for personal banking purposes. When I log on to my bank, I copy the latest lines of my statement and paste into my workbook. I use Paste Special | Unicode text and my columns are filled appropriately. The only problem is that the last column, i.e. the balance, does not format to two decimal places as I had already set up. How can I automatically paste in the right format, please? Do I need a macro? After pasting your data: Select the entire column (click the column heading) Find the button on the "Home" tab (xl2007) or on a toolbar (xl2003) that looks somew...

Keeping one of 2 cells always zero
Hi all of you, I have such a requirement. There are 2 cells in which the user ca input values. However, one of the two cells must always be zero fo valid inputs. Is there any way to do this? I was thinking of setting other cell to zero when user enters value i one cell or prohibit user from entering value in a cell till the othe is made zero but I don't seem to figure out how to do this. Is a macro the only way out (trapping a worksheet change?)? Is i possible to use someother functionality like data validation. I woul also like to impose restrictions on the non-zero cell of values betw...

Seed date formats to different year in different cells
Situation: Workbook was created in Excel 2003 (PC) and forwarded to another person who opened it with another PC using either Excel 2000 or 2003 (unconfirmed which version). The dates showed up as about 4 years off. My first thought? Maybe the machine was set with a different system clock. But here is where it gets weird; the receipient returned the workbook via email, and the wrong dates still show up even on the originator's PC. When he enters new dates, they show up as correct, but the cells that had the original dates (or anywhere they are cut/paste from those original cells, includi...

CSV File
I am opening a common delimted file in Excel and formating 2 fields as "text" to retain leading zeros. They appear correctly in Excel. I then need to save as a "csv" file and retain the leading zeros. How can I do this? The zero's always drop off in the csv file when retrieved again in excel. TIA hi, do you need to save the csv every time, if you save I agree it will remove the zeros. There is no perfect solution to this but if your file format is standard, you may consider preparing a excel template with the repeate, concencanate and left and right formul...

how do I convert office publication to word format
I'm trying to E-mail my publication home so I can print on my home printer Convert it to pdf format.. this is a useful page for info on doing the above.. http://www.microsoft.com/office/previous/xp/columns/column16.asp -- Mike Hall MVP - Windows Shell/User "sedonasdad" <sedonasdad@discussions.microsoft.com> wrote in message news:398B19D6-7CBF-4FA7-8EFD-2ED3F83EE50C@microsoft.com... > I'm trying to E-mail my publication home so I can print on my home printer You do realize that when you save your .pub file as a Word doc, you will lose your graphics and fo...

I need to F9 one cell in excel not entire worksheet
I have a very complicated worksheet. So when I change numbers it takes so long for this one cell to recalculate. I need a F9 key for one specific cell (Like word to update) But all I can find is entire worksheet or workbooks. Please help Hi AFAIK this is not possible -- Regards Frank Kabel Frankfurt, Germany "Paula" <Paula@discussions.microsoft.com> schrieb im Newsbeitrag news:C8FD806D-6CA6-405B-B6E0-C785DE88EA69@microsoft.com... > I have a very complicated worksheet. So when I change numbers it takes so > long for this one cell to recalculate. I need a F9 key f...

My Custom Line-Column 2 axes Chart dsn't work with +6sets of cells
I created a custom Line-Column 2 axes chart with 6 columns and it worked fine, showing two different Y axis. Then I tried to do the exact same process with a larger data set, with 10 rows again, but this time 12 columns, and it doensn't work - it repeats the 12 columns twice, instead of recognizing that I want the 2 sets of 12 on top of one another, like 2 layers, each with a different Y axis. Any ideas on how to avoid this? I think it may be a flaw in the software because, literally, when I delete half the cells and run the same process, it works... it just doen'st seem t o kno...

Fill leading zeros
I have a SELECT statement that I want to fill leading zeros in one column and trailing blanks in another column up to xx characters. For example SELECT CAST(EmployeeNumber AS varchar(8)) If the EmployeeNumber is 1234 I want to output 00001234 SELECT CAST(LastName AS varchar(15)) If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space Could this be done by casting a CHAR(15)? Thanks. -- David DavidC wrote: > SELECT CAST(EmployeeNumber AS varchar(8)) > If the EmployeeNumber is 1234 I want to output 00001234 SELECT RIGHT('00000000' + CAS...

Generate Leads/Accounts/Cases from a public website?
Greetings all, My employer asked me to investigate using a public .Net website to help generate information to be used in CRM. Specifically, he'd like our website to have webforms that, when filled out and submitted, would generate leads, accounts, or cases depending on what our needs are (I suspect generating leads would suffice for now). Can anyone point me in the right direction on how to accomplish this? I've found it difficult to find information online concerning this. We're currently using CRM 3.0 although I'd be interested to know what differences would be in...

Formating Cells In 2003
Hello, I have a strange question and am looking for some help to answer this. I have two machines, both running Office 2003 SP3. On one machine when I format a cell to either Numbers or Currency I get the display to show brackets () however on the other machine there are no Brackets shown. Both Office versions are exactly the same, both machines are running XP SP3. This is causing problems as on both machines worksheets need to be created and when the one formats the cells they do not get the brackets which are required. Can anyone shed some light on this for me? Chee...

Date format in the footer
Can one change the date format in the footer in Excel? Only if you use VBA to load the footer. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "JohnH" <anonymous@discussions.microsoft.com> wrote in message news:3E9C1FCE-2DCC-485A-BB4A-C40986AA70EB@microsoft.com... > Can one change the date format in the footer in Excel? ...