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
571 Views

Similar Articles

[PageSpeed] 7

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:

get lowest positive number
I have a cell that has number ranges from -37 to 88, what is the formula to show the lowest positive number. Thank You try this. It is an array formula so must be entered/edited with ctrl+shift+enter and will not work on full columns. =MIN(IF(D1:D100>0,D1:D100)) -- Don Guillett SalesAid Software donaldb@281.com "Tommy" <Tommy@discussions.microsoft.com> wrote in message news:D97567B8-E81D-48A6-B6F8-1B8CFF668646@microsoft.com... > I have a cell that has number ranges from -37 to 88, what is the formula to > show the lowest positive number. > > Thank You No...

Displaying Text, not numbers, in Pivot Table
Hello all. I posted this in the general section but I think it also could apply to here since I am new with using Pivot Tables. Currently I have a database in excel which has been made into a pivot table. It is just a simple catalog of some books. The row fields are organized as category, field, sub field and then title. The data items are a brief summary, catalog number, and where the book is currently located (in the office or someone else has it). My problem is all of the items in the data field are currently being displayed as numbers, which are all 1s since it gets that specific with t...

Sales Return with Lot Number
Hi All, I have spent almost 4 days now tryingt o figure out whats wrong, search every document realated to Sales Return , SOP Integration...... but of no help.... I am trying to integrate the Returns in SOP Transaction Entry Screen using Integration Manager. we are running GP10 with SP1. Issue Example: Item No: TEST101 UofMSchedule: EACH UofMSchedule Master Table: EACH = 1 = EACH CASE = 10 = EACH PACK = 20 = EACH I am able to integrate all the items with lot as return Returned Qty = On Hand. if the item UOM = EACH (which is actullay Base UOM). But if the same item is returned in C...

Receivables Statement Ranges
How can I get the User Defined Field 2 into the drop down box for Ranges on the Print Receivables Statements page? I see the customize User Defined Field 1 in there but not the second after I have customized it and added data into the field. Is there something I am missing? Thanks! No, the first field is automatically provided in the search but not the second. This would require a modification by a programmer -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublicat...

lable ranges in Excell
I am working with a customer list that I composed in excell to allow me to set up mailers & keep track of their address,dob etc I need it in out look so I can sink my new ipack so I dont have to enter all this data buy hand. In out look it says that you can import the data but it will not as it comes up with " the microsoft file ....................................... has nonamed ranges of tha data you want to import". I found where to name the rnages ( next to the address bar in excell but I am not sure of what to do. The office assent is pointing me to make them a "...

Creat a chart to show peak usage times for a resource
I'm no genius at Excel (2002 or 2003)and I could really use some help on the best way to display some data I have. What it shows is the usage of a training drop-in room, with details of the arrival time and period spent / per user / per day. There seems far too much data to put into a chart in any way that I can think of, but I really would like to be able to illustrate the spread and volume of clients throughout each day. Please can anyone give me simplistic information on how best to creat this as a visual report so that we can easily summarise usage information? Any ideas would be ap...

Need to change pic viewer in Excel
When I was running XP Home I could open a link to a picture in Windows FAX viewer. But in running XP Pro the picture opens in IE which causes multiple browser windows to close if viewing several linked pictures. This is an Excel newsgroup. You should post your question to a WindowsXP Pro newsgroup. HTH Otto "rgroff" <rgroff@discussions.microsoft.com> wrote in message news:46469ECB-F3B4-4F33-9FAC-8496239F14C2@microsoft.com... > When I was running XP Home I could open a link to a picture in Windows FAX > viewer. But in running XP Pro the picture opens in IE which...

numbering macro
In a database of say 10 columns and 100 rows, how do I record a macro that will o Insert a new column to the left of column A, then o in the new column A, number the rows that contain data in column B. Note: The numbering should start from 1. Since row 1 has the database heading & row 2 has the column headings, the numbering (no. 1) should start from row 3 and continue down to the last row that has data in column B. Columns("A:A").Insert For i = 1 to 100 If Cells(i,"B").Value <> "" Then Cells(i,"A&q...

counting numbers 04-09-10
Hi All, I have a Query like This. Pls Give your Suggestions. DATA: anil 0 sunil 1 anil 1 sunil 0 anil 1 sunil 0 Ans: Anil 2 Sunil 1 I need to count the numbers against name which number is more that 0. Regards, Anil. Maybe this =SUMIF(A1:A6,"Anil",B1:B6) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "showry" wrote: > Hi All, > > I have a Query like This. Pls Give your Suggestions. >...

Counting characters in a range of cells
Is there a way to count to number of times a given charachter occurs in a range of cells? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ David, If it's a worksheet function you want, try =SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"e",""))) which is an array formula, so enter with Ctrl-Shift-Enter. If you want to do it in VBA, use Debug.Print Application.Evaluate("SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,""e"",""...

IMAP service started but the protocol needs to be started under SysMGR
I have a W2K3 and Exchange 2003 server with no error messages in the event viewer. Even though IMAP is listed as started under Services, I had to go to Exchange System Manager to go to the server, select protocols and then start. There are no error messages in the Event viewer - can anyone tell me how to start this service automatically? It is currently set for automatic in Services but that isn't doing it (even though it says started) If you stop the service with the virtual server online it should be online when the service is again started. You might have to refresh but it shoul...

I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. If if needs to stay a number with the same value, I don't think you can do it. If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: > I need to format the number cell so that it will sti...

Need help with a project sheet, need date to show/not show based o
Hello, I have the following formula in cell S2 =IF(B2="","",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) This takes a date in cell B2 and adds 31 days to it and displays it. What I need to do is to NOT have cell S2 show any date if anything is in cell D2. I'm not very adept at Vis Basic if that what it will take to do it. Thanks Max Hi, No need for code =IF(OR(B2="",D2<>""),"",DATE(YEAR(B2),MONTH(B2),DAY(B2)+31)) Mike "Max" wrote: > Hello, > > I have the following formula in cell ...

Need help with SQL
SELECT DISTINCTROW OUTLOOK_APPROVEt1.To, OUTLOOK_APPROVEt1.Subject, OUTLOOK_APPROVEt1.Received, OUTLOOK_APPROVEt1.Contents, OUTLOOK_APPROVEt1.CountOfImportance INTO OUTLOOK_APPROVEt2 FROM OUTLOOK_APPROVEt1 WHERE Received >= date_sub(curdate(), interval 1 month) and Received <= date_sub(curdate(), interval 1 day) WITH OWNERACCESS OPTION; I am having trouble with the Where portion. I would like for this query to reveal all the emails over the past physical month. Example February 1st I would like to be able to collect the data for all of January. Any help would be greatly...

add number of duplicate fields in a row or column and have total
I have a log of jobs done on a photocopier, and need to bill people for what they have used. the log is very long and have the user name and the number of colour and number of Black and White copies they have done. I need to know how much colour and how much black and white jobs have been done by each user. hope someone know a way thanks or am I better to use Access ? J =SUMPRODUCT(($A$1:$A$20="Bill")*($C$1:$C$20)*ISNUMBER(FIND({"C","B"},$B$1:$B $20))*{3,2}) THe assumptions made People in A1:A20 Type in B1:B20, C for colour, B for B&W Quantity in...

I need to know on the structure of data of Manufacturing Order Processing
...

Balance showing as #####
I have a simple set up - date - debit - credit - balance. My total has now gone to over 10,000 and it is showing up as ######## - how do I correct this in very simple terms please as someone helped me in setting this up and I am not too familiar with Excel 2003. Thanks.. "Terri" <terri@mylanusa.com> wrote in message news:e3XCGLAdJHA.1188@TK2MSFTNGP05.phx.gbl... >I have a simple set up - date - debit - credit - balance. My total has now >gone to over 10,000 and it is showing up as ######## - how do I correct >this in very simple terms please as someone helped ...

If Count is not in a certain range, delete Rows
I have a spreadsheet that is 59752 x 2, with the one column Contact ID A and the other column Contact Link ID. It is like an instant messaging system. It looks something like this: 1 2 1 3 1 15 2 1 2 15 I want to count by different values in column A, and any count that is not within one of several ranges, those rows get deleted. So if the range is 3-5 contacts, then I want to keep the set of 1s, but delete the set of 2s. My ranges are 4-5 contacts, 30-41 contacts, and 100+ contacts. I am also brand new to macros, but I have experience with some other coding ...

Display ranges from one worksheet to another
I am running excel 2003. I have been given the task of creating a workbook where on one main sheet I want to display named ranges from up to 4 other worksheets based on the user selection. The ranges are work weeks and we want to be able to display 8 weeks at a time for resource planning. Can someone point me in a direction where I can find a way to do this? Thank you. Check Ron's site under the Copy/Paste section. http://www.rondebruin.nl/tips.htm "Opal" <tmwelton@hotmail.com> wrote in message news:eb392038-e6ab-46b0-a3d6-eb0df5c7a0fd@h2g2000vbd...

Re-order Number
Hi IS there anyway you can enter the re-order Number at the Matrix Stage of an Item, instead on every Item individually? If you enter it on the Purchase Order side it doesnt update the Individual item???? H ...

Web Service Reference not showing XSD
Hi, I just started playing around with web services. When I reference my Web Service in my project, the XSD classes don't get referenced (they don't show). When I switch to class view in my WEb Services rpoject I see them fine. But not when the web service is referenced. What's missing? Pat ...

number setting for workbook e.g. i want , instead of . in number
I want my excel should display number in US format not i German format. How to change number display setting for whole excel file. Check out Start>Settings>Control Panel> Date, Time, Language, and Regional Options> Regional and Language Options>click 'Customize' button If this post helps click Yes --------------- Jacob Skaria "Rajesh" wrote: > I want my excel should display number in US format not i German format. > > How to change number display setting for whole excel file. ...

Number cells won't reformat / sort
I'm dealing w/ a spreadsheet composed of cut-and-pasted rows from 2 sources (A and B) both originally exported from Filemaker at different times. When trying to sort the data by a "Record Number" field, the 2 datasets remain split and will not integrate. In other words, instead of the sort result being something like: # Code 1 WEX 3 WAF 4 WAF 6 WEX 7 WAF 9 WEX They look like this: # Code 1 WEX 6 WEX 9 WEX 3 WAF 4 WAF 7 WAF The 2 groups remain separate and sort individually. It gets stranger, though. If I reformat the column to, say, a percent or a currency r...

Can Excel 2007 open a numbers file?
Just wondering if there's an add-in that will let Excel open a numbers file, thanks. What is a numbers file? Gord Dibben MS Excel MVP On Wed, 20 Jan 2010 15:17:01 -0800, fnight <fnight@discussions.microsoft.com> wrote: >Just wondering if there's an add-in that will let Excel open a numbers file, >thanks. fnight - If you're referring to Numbers, part of iWork for Mac computers, I am not aware of such an add-in. The usual workaround is to use Numbers to save the file in a format that Excel can open. The web page http://www.apple.com/iw...

Sent Faxes in CRM 3 don't show in History?
I'm using CRM 3 under SBS 2003. When I send a fax using the "New activity / Fax" option within a contact record it sends successfully. . However there is no record of it stored in the contact history? If I check the fax console on the server the fax has been sent (also verified by receiving person) however it doesn't get logged in the history. I can't seem to find any record of it except on the Fax Console on the server?? Anyone else have this problem or no how to solve it? ps: As a workaround I thought I might be able to automatically route faxes sent back th...