Leading zeros with text #3

Hi,

I need help on specifying the maximum length of characters contained in a 
cell. Additionally, once the length is specified, I need the data contained 
in the cell to fill the maximum allowable characters for the cell with 
leading zeros if the data does not satisfy the maximum length. 

i.e. if I have a name such as gray in a cell and the cell is only allowed to 
be 13 characters long, how do I get a return of 000000000gray
0
ginzbeez (3)
10/22/2004 4:37:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
409 Views

Similar Articles

[PageSpeed] 35

If it's just a matter of formatting to look that way, go to cells, format, 
number, custom, '0000000000000'

"ginzbeez" wrote:

> Hi,
> 
> I need help on specifying the maximum length of characters contained in a 
> cell. Additionally, once the length is specified, I need the data contained 
> in the cell to fill the maximum allowable characters for the cell with 
> leading zeros if the data does not satisfy the maximum length. 
> 
> i.e. if I have a name such as gray in a cell and the cell is only allowed to 
> be 13 characters long, how do I get a return of 000000000gray
0
Jennifer1 (141)
10/22/2004 8:35:04 PM
Reply:

Similar Artilces:

Use of TTF in Text Box
Our logo has been set up as a ttf. Until now there's been no problem using this font in a text box in publisher. In 2003 the ttf will not print, it gives the default font. Word Art is OK, although patchy. Is there a setting somewhere I've missed?? Andy more than likely the TTF file you have does not conform to the TrueType standard. There are no issues with fonts in XP or Publisher 2003. Get the font re-done by a company that knows what they are doing. -- Font works just fine in Windows 2003 and Excel 2003. Locl supplier says it checks out. Still think this is a Pub 2003...

POP3 Question #3
We have SBS 2003 and ISA 2004, with Outlook 2003 running on the workstations. We are using Exchange for corporate email, but each user also has several different pop3 email addresses at different ISP's we want to send and receive with (Old email address, Boss's personal email, etc). Right now I am using Outlook to add these additional pop3 accounts for the users, which is working fine. Except that I would like to take advantage of Exchange's spam filtering capabilities and centralized administration of the accounts. Should I be using the POP3 Connector of Exchange i...

Creating a text box link
I am having a problem creating a text box link. I am using the blank publication, book fold option. I have a document in Word with graphics set up in 2 column format. I copied and pasted just portions of the Word doc into a text boxt in the pub along with a couple of graphics. I now have an overflow situation as I knew I would. The overflow symbol is showing at the bottom. I selected the text box and the handle displayed but the "Create text box link" icon is grayed out. What am I doing wrong? Help is greatly appreciated. Thanks -- David Frazier You have to create ...

How Should I Handle Large Amounts of Varying Text in Excel???
I have been asked to make a template that will contain formulas that must be protected because some of the users have few computer skills. The template also contains several areas that the user must type large amounts of explanatory text (which may vary from one sentence to several paragraphs). I do not know how to handle large amounts of text in Excel. If I protect the form, it disables the user's ability to re-size the row height in order to display all the text. Protecting the form also disables their ability to insert additional rows (and an Excel cell will only display 1,024 char...

bar chart #3
I am trying to make a bar chart. My problem is that excell keeps combining info from 2 different projects and inserts both of them into one bar chart. How do I fix this problem? I assume that you want to see the data as two series in one chart? Put your different projects into different rows, or preferably different columns, select them both, and the Chart wizard should split them into two different series. If that doesn't work, select one series, insert the chart, then go to source data, & add the second series. -- David Biddulph "Ann" <Ann@discussions.micro...

email text
I have set up an email signature that comes up every time I create a new message, when I type, no characters appear but the curser moves each time I press a key. How can I make characters appear on screen. ...

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 ...

How Install .Netframework 3.5 SP3 KB979909 & KB982157
I keep getting presented these updates and they always fail to install. I do not know what they do. Do I need them or would the best solution be to hide them? -- Uncle John My System XP_Pro_SP3 and OFFICE_Pro_2007 otherwise fully updated You could try installing them manually after downloading from the MS Download Center. -=- See the "How to obtain help" section of http://support.microsoft.com/kb/979909> For home users, no-charge support is available by calling 1-866-PCSAFETY (1-866-727-2338; and/or 1-866-234-6020 and/or 1-800-936-5700) in the United S...

Publisher 2003 Mail Merge #3
I have managed to shrink a certificate template down to fit two on one page, however when I do a mail merge, it prints the same person's name on both certificates. Is there a way I can have it print the next person's name in the mail merge on the second certificate? -- Matilda You need to have only one certificate on your screen for the merge to be successful. Look how the certificates are arranged when you have two on your screen, in a new publication create a custom size in page setup, correct the margins and gaps so Publisher will say "prints two to a sheet." Prin...

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...

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 ...

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 ...

Images blurr when grouped with text 01-08-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Images used as figures in a scientific paper imported in png format from Illustrator CS4 become blurry when grouped with the figure caption text. Happens when images are placed by drag and drop, cut and paste and when using the Inser>Picture>From file menu item. <br><br>This only happens in Word 2008 and unfortunately I've deleted my older Word vesion. <br><br>Any suggestions to fix before I switch to Pages by Apple? This is a multi-part message in MIME format. -----------...

Text "=SMTP:" appended to email addresses
Not sure if this is a server or an Outlook issue, so please bear with me if in wrong place! I've got one user (Outlook 2007) who is finding that when she replies to an email, the text "=SMTP:" is appended to the front of the destination email address, which renders the email undeliverable. ? Any ideas ? "Baz" <Baz@discussions.microsoft.com> wrote in message news:4E9CC0D5-B7C9-44AF-BFB9-54190CEBA96B@microsoft.com... > Not sure if this is a server or an Outlook issue, so please bear with me if > in wrong place! > > I've got one ...

Time Difference #3
I am trying to subtract (=B1-A1) 12:00AM or PM (B1) minus 1:00PM (A1) but some error comes with a bunch of ####? If my shift started at 1:00PM in the afternoon, and didn't end until 12:00AM in the mourning. How do I find the difference? Hi! Try this: =B1-A1+(B1<A1) Biff "Gabe" <Gabe@discussions.microsoft.com> wrote in message news:08A96454-4C34-4539-B653-70298ABBA8C7@microsoft.com... >I am trying to subtract (=B1-A1) 12:00AM or PM (B1) minus 1:00PM (A1) but > some error comes with a bunch of ####? If my shift started at 1:00PM in > the > afternoon, ...

Filter for 225 or 233 numbers #3
Hi again, just like to say thanks for all your help, really appreciated. I think I may have got it working by putting a 5 in the code ie =OR(MID(A1,5,3)="233",MID(A1,5,3)="225") instead of ==OR(MID(A1,4,3)="233",MID(A1,4,3)="225 and the same for your one frank but does this mean that it counts fro the left ? sometimes the no.s come in like this 92254898 or 792335698 ? -- hogan ----------------------------------------------------------------------- hoganc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1498 View this ...

How to extract a word from a text file
I need help to create a small batch script /procedure that extracts a piece of information from a text file: -Skipp first two lines in a file -Extract a piece of text from the 11-th character until the first blank / space character and store it into an environment variable. - Compare this info against a constant Any examples ideas please ? TIA Please post your query here: news://msnews.microsoft.com/microsoft.public.scripting.vbscript hth "TIA" <TIA@discussions.microsoft.com> wrote in message news:416630B6-8A60-4B7E-AB50-1818361CEA66@microsoft.com......

If "text" exist within "cell" then TRUE
I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE". I have a list that looks something like: Archetype Skill Dancer Acrobatics, Dance, Seduction Repairman Mechanics, Technology, Security And a cell where I can define an Archetype, such as: A1=Dancer I am trying to create a formula that's something like: If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing&...

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...

is there a special way to centre pictures/text on the page
is there a special way to centre pictures/text on your page, Like centre horizontally or vertically, any options like that? cause i cant find them Mattias wrote: > is there a special way to centre pictures/text on your page, > Like centre horizontally or vertically, any options like that? cause > i cant find them ===================================== Which version of MS Publisher are you using? Try the options at: Arrange / Align or Distribute... -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://support.microsoft.com/ph/...

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...

Changing text in title bar
I want to change the display in my opportunity title bars. Basically, I want to be able to do something similar to contacts. In contacts form, both the first name and surname are displayed in the title bar as a full name. In the opportunity form, I want to change the display so both the opportunity title and the job number it has been assigned are shown. I realise this may be difficult to change in an existing form, but I'm hoping someone can help. Failing that, I have another custom form that I want the same functionality for (i.e. the primary field of the entity plus the value of the...

moving files #3
how do I move outlook back-up files to a new computer the old one <anonymous@discussions.microsoft.com> wrote: > how do I move outlook back-up files to a new computer You don't need to ask more than once. http://www.slipstick.com/config/backup.htm -- Brian Tillman ...

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...

Text disappearing from cells and comments?
I recently opened 3 separate excel files created months apart and the text and background on the third row was not visible. Yet is was there when I rolled over it. At the same time the text in my comments are not visible either. Has anyone else had this problem? regards, Toby It sounds like the font colour is the same as the background colour. Try changing one of them. Rgds, ScottO "tlkcpa" <tlkcpa@discussions.microsoft.com> wrote in message news:6D496941-1209-43D1-B8F1-8C8274CB1D11@microsoft.com... | I recently opened 3 separate excel files created month...