leading zeros are not showing in the formula bar but do show in ce

I am working on an excel spreadsheet where I was provided a list of Social 
Security Numbers.  The SSN's that lead with zeros show up in the cell with 
the proper nine digit specification (no dashes or anything), because I 
formatted it to text.  However, in the formula bar (where it counts), it does 
not show the leading zeros.  

How can I get the formula bar to show all nine digits including the leading 
zeros?

Thanks!
-1
debbie4402 (120)
9/30/2005 7:38:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
961 Views

Similar Articles

[PageSpeed] 27

If they aren't showing up in the formula bar they aren't there.  You probably 
have a custom format of "000000000" or something similar.  To append the 
zero's you can use this function:

=CONCATENATE(REPT("0",9-LEN(A1)),A1)


-- 
Regards,
Dave


"Debbie" wrote:

> I am working on an excel spreadsheet where I was provided a list of Social 
> Security Numbers.  The SSN's that lead with zeros show up in the cell with 
> the proper nine digit specification (no dashes or anything), because I 
> formatted it to text.  However, in the formula bar (where it counts), it does 
> not show the leading zeros.  
> 
> How can I get the formula bar to show all nine digits including the leading 
> zeros?
> 
> Thanks!
0
9/30/2005 7:48:02 PM
I've dealt with that before, format the cells as text, that will prevent any 
leading zeroes from disappearing.  You will now have a green triangle in the 
upper left hand corner.  That just means you have numbers in a cell formatted 
for text.  Your not adding or manipulating the numbers so don't worry about 
it.

"Debbie" wrote:

> I am working on an excel spreadsheet where I was provided a list of Social 
> Security Numbers.  The SSN's that lead with zeros show up in the cell with 
> the proper nine digit specification (no dashes or anything), because I 
> formatted it to text.  However, in the formula bar (where it counts), it does 
> not show the leading zeros.  
> 
> How can I get the formula bar to show all nine digits including the leading 
> zeros?
> 
> Thanks!
0
BudHughes (9)
10/2/2005 2:26:02 PM
Reply:

Similar Artilces:

IF FORMULA 04-06-10
Hi: I am trying to write an if formula that if B2 is equal to a specific date in a range of dates then it is the end of a pay period, if B2 is equal to a date in a second range of dates then it state pay day. I have set up two tables with dates. One has pay period ending dates and the other table has pay day dates. I keep getting a Value error. Please help. -- Donna =IF(COUNTIF(A1:A10,B2),"end of pay period",IF(COUNTIF(C1:C10,B2),"pay day","")) Change ranges to suit. -- Regards Dave Hawley www.ozgrid.com "Donna" ...

How do add columns showing dob and years to work to give a retire.
I have a column showing date of birth and a column showing at what age they retire, 50, 55, 60. How do I add up the two to get a retirement date? Many thanks Claire Hi something like =DATE(YEAR(A1)+50,MONTH(A1),DAY(A1)) -- Regards Frank Kabel Frankfurt, Germany "Claire Le Monnier" <ClaireLeMonnier@discussions.microsoft.com> schrieb im Newsbeitrag news:8F892F61-F652-4177-A0C0-F25899C11CFA@microsoft.com... > I have a column showing date of birth and a column showing at what age they > retire, 50, 55, 60. How do I add up the two to get a retirement date? > Many than...

"De-name" cell references in formulas
Hello, I'm working in Excel 2003 SP2, and I'm trying to replace all references to named ranges used within my formulas within a workbook with the underlying absolute cell references. I found a solution in the archives of this newsgroup ( http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc ) , but I can't get it to work (neither the manual version nor the macro version). My only guess is that the behavior has of TransitionFormEntry has changed in Excel 2003. If so, is anyone aware of any other solutions. I found...

IF formula to round up values depending upon their outcome
I am using an IF formula to calculate between two cells, one is J (width) the other is K (length). Currently these formulas give an answer that then has to be rounded up based on the decimal place. I need the formula to also round up the amount to quarter increments. For example if the answer is 1.17 then the formula needs to make it 1.25, if it is 1.33 then the formula needs to make it 1.5, and finally if it is 1.63 then the formula needs to make it 1.75. So how do I add or make the formula round up to quarter increments? The formula that I am using is: =IF(J41<=3,K41/4,IF(J4...

Lead Import Issue
When you go to import a batch of leads, it seems in 3.0 that you cannot ignore certain source fields to import. Also the mapping screen is extremely odd to work with. Anyone have some other experience with this? Sean I have used the import wizard heavily so please let me know your issues as I can probably answer them. What issues are you seeing? ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Sean Fullerton" <stfullerton-no.spam@hotmail.com.nospam> wrote in message news:OEqDKcsKGHA.1288@TK2MSFTNGP09.phx.gbl... &...

Formatting lost when copied by formula!
Hi, I'm having a problem retaining format in a target cell where a formul "copies" the contents from another cell. Here's an example tha describes the problem in detail: Say I have the following text in cell A1: "Some text." Say, also, tha the word "text" is bolded. In cell B2 I have a formula, =A1, tha "copies" the content of cell A1. I can see the content of cell A1 i B2, but without the bold (or any other formatting, including backgroun color, etc.). My question, of course, is how do I retain the original formatting? Thank you -- Message ...

calculation formulas
Since today I am facing some peculiar change in my excel files. When I drag a formula from one cell to another cell, result of the first cell itself are reflected in the subsequent cells. Then I have to press F9 key so that all cells give proper calculation of the formulas. E.g Product Price Qty Value A 2 10 20 B 5 15 20 C 10 20 20 D 4 2 20 E 6 5 20 I did the above table in exel. Cell D2 was the result of multiplication of B2 & C2. Thats is correct. The I dragged the formula from D2 to D6. However in this res...

Can I use a worksheet name in a formula?
I'm using Office 2007 I have a workbook with 20+ pages. On one of them I want to use a formula that refers to a worksheet name, but in different cells I want it to refer to different worksheets. I have a cell that currently has a name typed into it that matches a worksheet name. Can I refer to the contents of that cell (INDIRECT) and use the content to refer to the correct worksheet and then go to that worksheet for the data? eg. Sheet called Christmas. In cell H2 value = Pudding on another sheet cell B2 value = Christmas. I want the formula in cell J12 to go to Sheet C...

Formula #23
I have some data in column A : 100, 102, 105, 110 I have data in cell B1 : 103 I want to check whether value 103 comes in range 100, 102, 105, 110. Here in the given case it has come as value 105 appears which include 103. I need a formula to check when value 103 arrives or arrived in tha range. in given case it arrived at 105. Please help me. Thank -- Message posted from http://www.ExcelForum.com Hi not quite sure but maybe =IF(AND(B1>=MIN(A1:A10),B1<=MAX(A1:A10)),"within range","out of range") -- Regards Frank Kabel Frankfurt, Germany > I have some d...

how do I get the FROM field to not show
I have a user who is using Outlook 2003 on XP Pro. when she starts a new email she has: From: To: CC: Subject: How do I get rid of the "From:" field? She has no idea how she turned it on and I can't see how to turn it off.... Thanks Kelvin "Kelvin" <me@mydomain.com> wrote in message news:elzCUTEpKHA.1552@TK2MSFTNGP05.phx.gbl... >I have a user who is using Outlook 2003 on XP Pro. > when she starts a new email she has: > From: > To: > CC: > Subject: > > How do I get rid of the "From:" field? If...

I want my INBOX messages to arrive showing FROM, not TO:
I want my INBOX messages to arrive showing FROM, not TO: Obviously the messages arriving are to ME...one of my profiles is normal and shows who the message is from , but with on eof my profiles, a new message is displayed showing it is TO and my email...I want it to display FROM: and whoever they are. Hi, You can customize the view for each Outlook folder to display only the fields you want to see, as well as in what order you would like the information displayed. To change your view, open the Outlook folder (in your case, the Inbox). Right click on the gray title bar and click on ...

My Live Meeting outlook add-in does not show up.
I have installed the Live Meeting Outlook add-in but for some reason it does not show up. Not sure what is going on? -- Chandana Hathi HArd to help without know what version of Outlook and what version of Windows. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Chandana_MS asked: | I have installed the Live Meeting Outlook add-in but for some reason | it does not show up. Not sure what is goin...

2nd Deposit not showing up in Bills and Deposits
Hello, Can anybody tell me why only one of my deposits shows up in my 'Bills and deposits' page when I have the deposit set up as 'Every other week'? I am viewing one month at a time. All the bills appear but only one deposit appears when it should, at least show two. Thanks M You declined to mention the version. This is exactly how M02 and up work. M01 and previous worked different. What version are you using? How long have you been using it? And prior to that you were using what version? "M" <anonymous@discussions.microsoft.com> wrote in message new...

Showing email addresses
Is there any way you can send emails where, when you send an email, no one can see the email address of any others you may have sent the email to? Can you format Outlook so that it will only show someone's name and not their address, even if you right click on their name? I know what I am trying to say, I just can't put it into words. I hope you understand what I am asking. ...

format one data series, with error bars, and use it as default
In Excel can I format one data series with custom plus and minus standard deviation error bars and custom colors and then have the chart use that as the default? I couln't use a format painter to do. JBR, Yes, once you've created the chart with your custom error bars and colors, save it to the Custom Types Gallery: (1) Select (active) the chart. (2) Go to Chart -> Chart Type -> Custom Types Tab -> User Defined. (3) Click the Add button and you should see the Add Custom Chart Type Dialog box. (4) Enter a name and optional description for your chart. (5) Click OK and...

renamed account shows old alias
We had a user, Brandy, leave and another, Angie, take her place. Whoever set Angie up in Exchange 2003 just took Brandy's account and renamed everything for Angie. The email account works fine but when you open a new email from any PC and start to type 'Angie' in the To box it populates the box with 'Angie <brandy>'. The message reaches Angie fine but it annoying to have the old alias still there. Where is that coming from and how do I get it to show <angie>? Our clients run Outlook 2003 but the issue seems to be with AD or Exchange 2003. TIA Larry ...

Formula to calculate and add tax
I am creating a simple (I thought) pricing guide and I am running int the circular reference problem. Here's what I want to do. I want t enter a price in a cell and have the tax automatically calculated an the total price returned to the cell. I know I can create an entirel new set of cells just to contain the price and the tax rate then hav that result displayed in the desired cell. But, I wondered if it i possible to enter the price directly into the cell. For example: In A1 A2 A3 I want to be able to enter the price before tax an then have the tax calculated and the total displa...

export query with formulas to excel
Is there a way to export my Access query to Excel 2003 while keeping the formulas in Excel. For example, in my Access query I have a field called "Other" which is defined as the following: Other: [DiffTotal]-[BudTo] [DiffTotal] and [BudTo] are "real" fields in the query and become columns in Excel. Instead of having the value of "Other" appear in Excel, I'd like the "Other" column to have the formula where, for example in row 1, it could look like "=A3-A2." Thanks, ~Mark When Access exports to Excel, it exports the...

Formula to Identify groups of numbers to be added.
Hoping I can get a cell formula that will calculate as follows: Cells in Column H have this relative formula in them. =3DIF(M12=3D"Yes", "",C12) This results in a cell will either show a number, or is blank. Each week or so, another entry is made to the next empty line. After awhile, Column H might look something like this: Number are in cells H12 =96 H14 There is a blank at H15. Then numbers in cells H16-H19, and a blank at H20 Column H Line12 13.89 Line 13 7.89 Line 14 6.14 Line 15 Blank cell (this shows up as blank cell) Line 16 ...

Viewing Leads
When leads are entered into the system and assigned an owner they are not visible toeverypne using the system. I am wondering if there is a way that I as sales manager can put in a lead that everyone including my sales staff and CEO can see and then call on. The lead is only visible to me and if I assign it to someone else, it is only visible to them. Is there a way to set it up so that everyone can view all leads no matter who they are assigned to? -- Christopher Deeds Director of Sales and Marketing ITechWest The security roles of the users would need to be set to 'Organizational&...

Array formula to Identify Leading Zeros.
I have a bunch of spreadsheets that have a sizable number of columns - usually about 128 columns. And I need to identify those columns that have leading zeros in them. Logically, If I knew how many cells had a zero in the left most position, I could tell which column had a leading zero. But with 128 columns and 500-12000 rows, this is too hard to just eyeball. I think an array formula will do the trick - but I'm not having much luck getting one to work. For example, in column B, cells B7:B478, I've tried something like: {=sum(countif(B7:B478,left(B7:B478,1)=0))} Am ...

Outlook Burped and HTML Format Now Shows
Suddenly, HTML formatting is showing on my Outlook new mail messages composed with HTML. How can I delete this without having to resort to plain text emails? JTLaBelle wrote on Mon, 29 December 2008 10:56 > Suddenly, HTML formatting is showing on my Outlook new mail messages composed > with HTML. How can I delete this without having to resort to plain text > emails? Is it showing on messages you receive or messages you compose? If when you compose, does Alt+F9 hide it? If messages you receive, what version of outlook? version of windows? do you have all the latest updates i...

Dialog Editor Shows the previous name (not ID) of the resource
may be i havent explain clearly i try to expose the problem in another way: the resource.h contains something like this: .... #define IDC_STATIC_GROUPBOX_ARRAY 1000 .... when i click on that Group box in dilaog designer these values are shown in the properties box (Under the Misc Category): name = IDC_STATIC_GROUPBOX_ARR //its some previous name of the resource ID = IDC_STATIC_GROUPBOX_ARRAY // the current and correct ID of the resource the name property is grayed out (meaning its a read only property) but the ID property is editable Questions: 1- where does the VS store the name pro...

Convert a lead to a contact without loosing the company name
When converting a lead to a contact you should be able to retain the company name. ---------------- 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 Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=dd31d3fd-af2c-408d-8156-61a3ef46d0e5&...

Odd Excel Formula Problem
I am building a spreadsheet tool that pulls data from either another sheet or another workbook. In both cases, when I write my formulas, the seem to work initially, but at some point something happens and instead of displaying the result of the formula, they display the formula as text. For example: In Cell A1 I type =if($A$2=$A$3,1,0) initially it display's my desired result ("0"). Then, if I go in and try to unlock the cells to copy down and across the formula result changes to "=if($A$2=$A$3,1,0)" I have never seen this error and can't figure...