Need trailing zeroes to show for range of numbers....

The file I pulled into Excel for some reason cut off 2 
trailing zeroes, no matter how I format it they will not 
show up.  There are thousands of them and I can't see 
typing in each one over just to get two zeroes on the 
end.  Does anyone know of a way to format this so they 
will show up?  I have searched Microsoft online with no 
help.
0
anonymous (74722)
2/13/2004 3:54:45 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
613 Views

Similar Articles

[PageSpeed] 49

Not too much information, but try this (after saving your workbook!)
In an empty cell enter the numner 100. Edit>Copy
Select your data
Edit>Paste Special, check Multiply

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Sueshe" <anonymous@discussions.microsoft.com> wrote in message
news:f5e201c3f249$b34565f0$a301280a@phx.gbl...
> The file I pulled into Excel for some reason cut off 2
> trailing zeroes, no matter how I format it they will not
> show up.  There are thousands of them and I can't see
> typing in each one over just to get two zeroes on the
> end.  Does anyone know of a way to format this so they
> will show up?  I have searched Microsoft online with no
> help.


0
nicolaus (2022)
2/13/2004 4:21:53 PM
>-----Original Message-----
>The file I pulled into Excel for some reason cut off 2 
>trailing zeroes, no matter how I format it they will not 
>show up.  There are thousands of them and I can't see 
>typing in each one over just to get two zeroes on the 
>end.  Does anyone know of a way to format this so they 
>will show up?  I have searched Microsoft online with no 
>help.
>.
Sueshe

The file must be Text so formatting the number will do no 
good. You must first convert it to a number in a helper 
column. Say the text number is in A2, in B2 type =A2*1
Copy this formula down then format whatever way you want. 
Select the formulas and Copy and Choose Edit, 
PasteSpecial, Values and this will convert the formulas to 
values. You can then delete the original text numbers.

If you do not mind using VB this sub will convert the text 
value to four (4) decimal places. (alter dp to suit)

Sub AddDec()
Dim c

For Each c In Selection
    c.Value = Round(c * 1, 4)
    c.NumberFormat = "0.0000"
Next c
End Sub


To operate the macro right-click the Sheet Tab and choose 
View Code. Paste the  macro into the module.
Then select the text numbers in the spreadsheet and 
swiching back to the code module press F5.

Regards
Peter
0
2/13/2004 4:32:00 PM
Worked like a charm....thank you so much...you have just 
saved me hours!!


>-----Original Message-----
>Not too much information, but try this (after saving your 
workbook!)
>In an empty cell enter the numner 100. Edit>Copy
>Select your data
>Edit>Paste Special, check Multiply
>
>-- 
>
>Kind Regards,
>
>Niek Otten
>
>Microsoft MVP - Excel
>
>"Sueshe" <anonymous@discussions.microsoft.com> wrote in 
message
>news:f5e201c3f249$b34565f0$a301280a@phx.gbl...
>> The file I pulled into Excel for some reason cut off 2
>> trailing zeroes, no matter how I format it they will not
>> show up.  There are thousands of them and I can't see
>> typing in each one over just to get two zeroes on the
>> end.  Does anyone know of a way to format this so they
>> will show up?  I have searched Microsoft online with no
>> help.
>
>
>.
>
0
anonymous (74722)
2/13/2004 4:43:51 PM
Reply:

Similar Artilces:

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

Set a range from a variable location
I am using a loop function that will eventually select and activate a specific cell known as "TheCurrentCell." Once I reach that location I want to use that coordinate as the upper left area of a new range. Supposing that the end cell of my range is D1000, the Range selection would be something like Range(TheCurrentCell:D1000).select, however, I don't know the correct syntax to make this happen. Please help. Thank you. Use something like Range(Selection.Address & ":D1000").Select. "Troubled User" wrote: > I am using a loop function that will...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Messenger emoticons
I have changed laptops and I did grab the old laptops custom emoticons folder (all in dt2 and id2 file endings.) But when i copy everything in the folder and add it to my new laptops custom emoticons folder... they get added (i.e. show up in the folder) but the images/gifs or names dont show up on the actual msn... *what gives*? Do I have to change the dt2 endings to gif or jpeg and go to "create" in msn for each of them to add them in? (I tried with one and it worked) Only problem is i have alot, like 203 dt2 files so changing the ending to .gif and adding each singu...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

re post range check
hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 -- -- --- -- -- --- - -- -- 31-12-04 floor ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

Range Names with Charts
I would like to use rangenames to specify the data sources for a chart, but Excel does not seem to like this. I have tried entering the rangename in the following formats: rangename =rangename ={rangename] ={"rangename"} .... and various other formats. Surely there is a way to do this ....? Art Hi, try either depending on whether the named range is local to the sheet. =sheetname!rangename =bookname.xls!rangename cheers Andy ArthurJ wrote: > I would like to use rangenames to specify the data sources for a chart, but > Excel does not seem to like this. I have tried en...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location. The code looks like this: Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut Range("D5").Insert(xlShiftToRight) However, when it reaches this point, I get an error saying "Cut method of Range class failed". I've tried changing the destination range so that it reads similar to the origin range, but that didn't help. Can someone help me get the syntax on this line correct? I've got several similar lines further down in the code. If I can fix thi...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was authored by someone else. Track Changes is switched off, all changes have been accepted, and everything looks as it should in whichever view I happen to choose (Print Layout, Draft, whatever). But when I click to show formatting symbols (in whatever view) a whole lot of old changes - deletions AND insertions, ostensibly all accepted, and from before the document got to me - appear in the document, making it quite tricky to work with. These old changes are impervious to anything I try to do with them E...

Need to show file properties dialog box
Hello I need to show the shell's file properties dialog box. I tried converting old vb6 code into vb.net but it gives me error. Here's my code: Private Structure SHELLEXECUTEINFO Dim cbSize As Long Dim fMask As Long Dim hwnd As Long Dim lpVerb As String Dim lpFile As String Dim lpParameters As String Dim lpDirectory As String Dim nShow As Long Dim hInstApp As Long Dim lpIDList As Long Dim lpClass As String Dim hkeyClass As Long Dim dwHotKey As Long D...

Stock splts don't show
Stock splits don't show in the investment account register. This makes it confusing. Yes, I know they show in the stock price area, but that takes many 'clicks' to get there. At a minium, stock splits should show when you click on investment activities for a particular stock. A stock split IS an investment activity. Money's nemesis Quicken shows a stock split on the account register! Feel free to send that in as a wish, it's quite a good idea IMHO -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK...

Email to multiple people but show only the recepient name
Is there a way to send to ten people at once but only have one name in the To: box at a time? So that the entire list of people is not sent. Use the BCC field. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Terry asked: | Is there a way to send to ten people at once but only | have one name in the To: box at a time? So that the | entire list of people is not sen...

unable to link from Access to excel named range error 3011
I have a list of xl workbook names in an Access table. My code cycles through these names and finds them in a directory. This works flawlessly. Now I want to find and name ranges in the workbooks, and link to them from my Access db. The process works for the first 2, then fails on the 3rd. I don't see what's different about the 3rd tblName lclName CellTxt RngNm Active and Approved Pending Stores*.xls xlStores Location Code Stores Annualized Rent Batch*.xls xlExtras Store Number Extras ...

Date/Time Show When Changes Made
Hi, When changes are made in a table or form, I want the date and time to appear showing me when changes were made in any of the fields. How can it work? MTIA. It's not possible if you're working directly with tables. Using foms, you have to put code into the form's BeforeUpdate event to update the row's LastChanged field (which, of course, you have to add to the table yourself) You might find what Allen Browne has at http://www.allenbrowne.com/AppAudit.html to be useful. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Beame...

Extra word prints that does not show when viewing the document
When I print my document or view it, this extra word is not there. When the document prints, this hidden word is in the printed document. How do I find this hidden word and delete it? If it prints, you know where it is. Have you turned on your nonprinting characters, etc? Have you tried copying and pasting it into a new blank document? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "S Roberts" <S Roberts@discussions.microsoft.com> wrote in message news:C437AFE5-8C0F-4859-8994-AA78D18CA573@mi...