Adding a leading zero "0" to a number in a cell

I am trying to create a formula that will add a leading zerto to any number 
that comes in with less than 5 digits. This is for a spreadsheet that will 
carry over zip codes for a mailing.  I want to know if I can write a formula 
and if so, what it is that if a column of numbers just has 234 that the 
formula will automatically add the two zeros making it 00234. Or if it is 
2345 it will convert it to 02345.

Also - will the leading zeros (for lack of a better term) be hard coded so 
that when you extor to a csv or another format, the zeros will remain.

Thanks 
-- 
Chip
0
Utf
2/22/2010 9:24:04 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1402 Views

Similar Articles

[PageSpeed] 58

supposing your 234 value is in cell A1, your formula would be:
=TEXT(A1,"00000")

Yes, these will be sustained through saving as CSV or other format, as they 
are now a text value.

"chipwood3" wrote:

> I am trying to create a formula that will add a leading zerto to any number 
> that comes in with less than 5 digits. This is for a spreadsheet that will 
> carry over zip codes for a mailing.  I want to know if I can write a formula 
> and if so, what it is that if a column of numbers just has 234 that the 
> formula will automatically add the two zeros making it 00234. Or if it is 
> 2345 it will convert it to 02345.
> 
> Also - will the leading zeros (for lack of a better term) be hard coded so 
> that when you extor to a csv or another format, the zeros will remain.
> 
> Thanks 
> -- 
> Chip
0
Utf
2/22/2010 11:12:01 PM
Select Number format then Number then Custom. Inset 5 zeros in the Type: Field.

I tested this in xl2007. Saving as a .csv file and then opened it with 
Notepad and the leading zeros were there.



-- 
Regards,

OssieMac


"chipwood3" wrote:

> I am trying to create a formula that will add a leading zerto to any number 
> that comes in with less than 5 digits. This is for a spreadsheet that will 
> carry over zip codes for a mailing.  I want to know if I can write a formula 
> and if so, what it is that if a column of numbers just has 234 that the 
> formula will automatically add the two zeros making it 00234. Or if it is 
> 2345 it will convert it to 02345.
> 
> Also - will the leading zeros (for lack of a better term) be hard coded so 
> that when you extor to a csv or another format, the zeros will remain.
> 
> Thanks 
> -- 
> Chip
0
Utf
2/22/2010 11:18:01 PM
Reply:

Similar Artilces:

Need help getting this code to compile under VS.NET 2003 (compiles under VS6.0).
I'm tasked with converting a LARGE project from VS6.0 to VS.NET 2003. Here is a VERY scaled down version of an error I'm getting. I must be getting rusty, but the way to get around this compiler error is just not coming to me. If you need to compile this, just create a Win32 console app and specify to add support for MFC, then copy this code into the cpp file and press compile. class CLogItem : public CObject { protected: virtual BOOL CheckConflict(CLogItem* pItem) const; }; struct MapEntry; //forward declaration.. typedef CArray<MapEntry, MapEntry&> CMapEntryArray;...

I get COMException while exporting chart on IIS 6.0.
I have this code that works perfectly on my development machine when I deployed it at customer site it gets exception. Here is the error and my code: thanks error ========== Exception from HRESULT: 0x800A03EC. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC. Source Error: An unhandled exception was generated during the execution of the c...

CRM 4.0 Outlook RPC pver http
hi, A customer already has SBS with CRM server running. Some employees have Outlook configured using RPC over http. These users can use CRM using the web client and need access thru outlook. Is RPC over http supported in CRM 4.0 ? What are the issues involved in getting Outlook to work with CRM 4.0 ? I am yet to review the implementation guide for this situation, any other blog or guide I need to refer ? Thanks in advance. On Aug 14, 1:53=A0am, ShawnK <Sha...@discussions.microsoft.com> wrote: > hi, > > A customer already has SBS with CRM server running. Some employees...

Covert v. 2000 -> v. 2.0
Heelo All after serious cooruption we recover dmb file with Ontrac tools to v. 2000 but application are for v 2.0 are is possible conversion to v. 2.0 thanks Ervins ...

Creating a Mail Merge template in 4.0
I am using the RC0 image for demo purposes and have not managed to do this. When editing the template in word I get the message "Record 1 contained too many data fields." If it is never going to work please just let me know and I will try to focus on teh live releasse. Cheers, Mike. I get the same behavior in the released version. I suspect the 'Edit Template in Word' operation is simply invoking the template as if it was doing a mail merge operation; because there is no data available (you are in the editor of the letter template afterall), the merge process get...

CRM 3.0 Installation
Has anyone been successful in installing CRM 3.0 onto an AMD64 bit processor using Windows 2003 x64 operating system? we are switching to DL145 servers for all f our web server machines and would like to migrate our CRM applications there also. I have not heard anything via official channels, but I don't believe CRM will install on x64 servers. The problem is that the environmental diagnostic in the CRM installation routine checks for MSXML 4.0 and doesn't find it. x64 Server requires MSXML 6.0, which, although "backwards compatible" does not satisfy the environmenta...

Exchange 2003 + Outlook, adding disclamier message
Hi all, As following KB317680 "How to add a disclaimer to outgoing SMTP messages in Visual Basic script". I noted that from the end of the KB: Note If you use a MAPI client such as Microsoft Outlook to send the e-mail, the recipient does not receive a modified message. This is because messages submitted using MAPI are not in SMTP format when the e-mail triggers the SMTP transport event. Therefore, changes that are made by the event's code are not persisted. What should i do if my site is using Microsoft Outlook to send and receive email, how to add the disclamier message? Pl...

Database changes from 1.3 to 2.0 affecting custom reports
I just installed an upgrade from 1.3 to 2.0. The customer has some custom reports that worked fine in 1.3, but don't run anymore in 2.0. I suspect that some of the table or field names are not the same. When we try and run one of the custom reports we get this error: Alter Table VIEWSTOREQTY and Alter Column DAYOFYEAR DATETIME" Then when I hit OK, I get this additional message "(5) Invalid Procedure call or argument." I have a copy of the custom report, but don't know how to identify which table names or fields have changed. Where would I find a list of tabl...

Adding a disclaimer to Exchange 2003
Can some point me to a kb or other know FREE option to add a disclaimer to outgoing e-mail? -- Stokes Read this first: http://slate.msn.com/id/2101561/ 1. http://support.microsoft.com/default.aspx?scid=kb;en-us;262097 2. http://www.exclaimer.co.uk/ 3. http://www.nemx.com 4. http://support.microsoft.com/default.aspx?scid=kb;en-us;317327 5. http://support.microsoft.com/default.aspx?scid=kb;en-us;317680 6. http://www.gfi.com/news/en/mes8freedisc.htm 7. http://www.isaserver.org/software/W2K/Anti-Virus/MailFender-for-Exchange/Comments/ -- Ed Crowley MVP - Exchange "Protecting...

Duplicate appointments in Outlook Calendar using CRM for Outlook 3.0
We have a few employees here who are getting duplicate appointments in their Outlook calendars. What is happening is that the assistant is creating a meeting on her calendar, and inviting several people. The meeting is also tracked in CRM. Everyone gets the meeting request and it appears on their calendars properly. However, two people will then check their calendars after a few minutes, and see a duplicate appointment, always 3 hours earlier. I believe this is coming from CRM when it synchronizes with their Outlook. Anyone know why this is happening and how to stop it? I suppose...

Adding A Video Clip In Front Page 2003
I have followed the instruction for imbedding. And inserting. When viewing the site, it takes forever to load though is not a problem in 'preview'. Do I need some kind of compression software? What format is the video clip? How big is the file? -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "Roadfiz" <Roadfiz@discussions.microsoft.com> wrote in message news:9112FAC2-7F69-45B3-BB8B-F6ECE2A87FF3@microsoft.com... :I have followed the instruction for imbedding. And inserting. When viewing : the site, it takes forever...

2.0 : fastest read-only forward-only loading method
In 2.0 what is the fastest way to iterate through an element set in a read-only forward-only XMLDocument ? For example , load the key/value pairs in the following xml doc into a hashtable : <Mappings> <Mapping MappingKey="AL" MappingValue="Alabama" /> <Mapping MappingKey="CA" MappingValue="Alabama" /> </Mappings> John A Grandy wrote: > In 2.0 what is the fastest way to iterate through an element set in a > read-only forward-only XMLDocument ? Create an XmlReader and read out the nodes you are looking for...

Crashes when adding new contact 2002
All of a sudden when I add a new contact and save it, I get the message: "Outlook has encountered a serious error and needs to close......." Outlook shuts down and restarts. Any suggestions? Thanks Tim -- yellowstone I'd suggest posting all the steps that occurred just before "all of a sudden." There are countless possibilities for this error message and we can't begin to help until you narrow them down. You were there. We weren't. Tell us the entire story. "All of a sudden" won't get you off the hook. -- Russ Valentine [MVP-Outlook]...

0 on both x and y axis
Hi how do I format the chart so that the plot will start at 0(where x axis intercepts y axis) on both x and y axis? thank you! AS - Use an XY (Scatter) chart type. For each axis, select it, and choose Format Selected Axis | Scale to specify where the other axis crosses. - Mike www.mikemiddleton.com "AS" <AS@discussions.microsoft.com> wrote in message news:644CEEDC-0632-40E9-80A8-38E79DDA91FC@microsoft.com... > Hi how do I format the chart so that the plot will start at 0(where x axis > intercepts y axis) on both x and y axis? thank you! ...

"Enter" moves the screen, not to next cell(excel07) help!
Using excel2007. the <Enter> key now moves my screen, rather than moving to the next cell. I don't know what changed, or how to return to sanity. please help! try to press Scroll Lock ONCE ! Micky "dcfolsom" wrote: > Using excel2007. the <Enter> key now moves my screen, rather than moving to > the next cell. I don't know what changed, or how to return to sanity. > > please help! ...

Money 2005 : Adding Short Positions to Investment Account ?
This is a multi-part message in MIME format. ------=_NextPart_000_0430_01C4AD72.004C3D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I recently started using Money 2005. I have a brokerage account that = does not support online synchronization with Money 2005.=20 There are two short positions in this account which I am trying to add = to Money after creating a new investment account. 300 shares short of XYZ 100 shares short of ABC How do I add these opening short positions ?=20 I tried the following but failed - Add Shares (-300) o...

SQL RS Authentication Issue
Setup: CRM 3.0 new install on SBS 2003 server. The CRM install is what installed SQL RS. Users (and administrator) can view list of reports in web client, but when they open a report and hit the 'Run Report' button they are prompted to enter their credentials. The Reports and ReportServer virtual directories are installed in the Default website. I had to do some tweaking after the "9-click" installation because it set the URL of the SQL Reporting server as servername.domain.com instead of servername.domain.local. -- Matt Wittemann http://icu-mscrm.blogspot.com It ...

Imail link for CRM 3.0
has any one explored links for Imail (ipswitch) and CRM 3.0, I expect the SMPT / POP3 connection will be simple. Kevin you will be able to send but not receive ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Spike" <Spike@discussions.microsoft.com> wrote in message news:1678CFF2-D6F8-4E26-B7FB-C204839AA152@microsoft.com... > has any one explored links for Imail (ipswitch) and CRM 3.0, I expect the > SMPT / POP3 connection will be simple. > > Kevin ...

Vlookup returns a "0"
I am using a vlookup that works mostly as I expect. That is, when there is a value to be returned, it returns the value - however, there are instances where no value will be returned, in some cases, Excel leaves the cell blank (as I prefer), in other cases, it populates the cell with a 0 (zero). What can I do to not have it populate with a zero? And why the discrepancy between the two behaviors? Please post your formula and examples of your table and search argument. How is the formula cell formatted? -- Kind regards, Niek Otten Microsoft MVP - Excel <mark.wolven@gmail.com> wrote i...

segfault! with CImage::GetBits() in CE 3.0/5.0 if LoadFromResource() is used, why???
If I use CImage::GetBits() to retrieve the pointer to the bits of a bitmap which was loaded with LoadFromResource(), any attempt to access that pointer results in a segfault! I've spent 5 hours on this (yeah I'm a little slow) and I realize it must be an undocumented "feature" of Windows CE. *Please* document this problem. When I checked the value of the pointer I was getting, I noticed it was "128" - sounds pretty low! Is that system memory maybe? Wherever it is, my app isn't allowed there. Code: CImage *image = new CImage(); if (!image) return -1; H...

MSCRM 3.0 Emails Not Sent from Laptop Client.. Works from Web Client
I'm attempting to chase down an issue with e-mail here. For some reason, our users using the Laptop client are experiencing an issue where after sending an e-mail through the E-Mail Activity, the e-mail appears properly in the History window, but the recipients never receive the message. >From the web client, this works without issue. I tested using both clients sending an e-mail activity to myself at both my internal corporate e-mail address and to my Gmail address. Both the e-mails from the web client arrived in their respective inboxes, the 2 sent from the laptop client did not. ...

Auto generating unique number
Hi We would like to customize CRM so that when an opportunity is created, a unique number is generated and this unique number appears throughout the sales process. This number has to be auto generated by CRM so that a user does not have to enter it manually. The number should follow automatically from the previous number. Can this be achieved by creating a workflow rule using a call assembly? Grateful for any assistance. Thanks ...

#DIV/0!
I have this error that shows up and it isn't something I want visible when I work with my file, and I want to know if there is a way to make it invisible? original formula: =$A$5/SUM($B$5:$B$103)*B5 I have already made the cell hidden/locked of course and even hid the zero's under TOOL/OPTION and nothing. Before I had some help on another file with similar problems and got help so I took the formula and tried to implement it to this one and this is how it went with no luck. =IF(ISERROR(ROUND($A$5/SUM($B$5:$B$103)*B5)),"",ROUND ($A$5/SUM($B$5:$B$103)*B5)) Please hel...

displaying a number like 1/8,000
I'm type 1/8000 in a cell and it converts it to decimal. I like to keep it in this fractional format How do you make it do that? I did choose fraction under cell formating menue, but it won't display things like 1/ 100,000 Hi try formating the cell with the custom format # ?/??????? -- Regards Frank Kabel Frankfurt, Germany Thrava wrote: > I'm type 1/8000 in a cell and it converts it to decimal. > I like to keep it in this fractional format > > How do you make it do that? I did choose fraction under > cell formating menue, but it won't display things ...

EX2003 -Deleting AD user acct doesn't delete addressl in GAL
Running Win & EXCH 2003 and new to both. Deleted user is still listed in GAL after delteing user via User Manger...AD. What did I miss? Hi Bruce, Thanks for your posting here. This issue is mostly related to the Recipient Update Service. The Recipient Update Service creates and maintains Exchange 2000-specific attribute values in Active Directory. If you create/delete a mailbox for a user, the Recipient Update Service is responsible for the automatic generation of their Simple Mail Transfer Protocol (SMTP) address and any other proxy addresses that you have defined for your reci...