The data I am working with comes out of a database and is formatted as text. I need to copy and paste the data a few times and then need to use it in a formula that will only recognize it if it is a number format. The problem is that when I convert it to number I loose the trailing zeros. Or if I right click and format cell and choose number, I have to specify the number of zeros after the decimal place, but this wont work for this large data set that I want to format correctly at once.
Welcome to the forum.
Your description is pretty good but to be able to give a good answer it would be much better to have examples of what you start with and what you expect as a result.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
If you are losing trailing zeros, then perhaps the conversion is not being done correctly. I can see how leading zero's would get dropped, but not trailing zeros. Can we see a few samples?
Maybe also consider doing the conversion IN the formula, instead of separately?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
This might be similar to this thread of 2012.
http://www.excelforum.com/excel-form...ing-zeros.html
Thanks for the info, however I am still having my problem. I will give you a little more background on the data, which is dissolved metal concentrations in water and the number of trailing zeros is important for this data. The data is stored as text in the database before the data is used to make an excel spreadsheet that I am working with. The data look range in values from 10.00 to .0045000 the trailing zeros are important to keep and they are all different so I cannot specify the number of zeros after the decimal place as you would do when you format the cell and change it from text to number. I cannot do this for each data value, and there is an error that comes up with my data in a diamond with an exclamation point saying that the number in this cell is formatted as text or preceeded by an apostrophe, which the latter is not the case. When I click on this error box I can choose the option to convert to number, but again I loose the trailing zeros. Any ideas?
Change celformat in 0000,0000
Add or remove zero's whatever you want.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Im sure you can explain this to us, but I really dont see what the difference is between 0.0045000 and 0.0045? As far as excel is concerned, they are the same. excel will not store a value in the format of 0.0045000, it will always drop the unnecessary zeros. If you want it to show to 7-or-8 decimal places, you need to format it that way.
If you are dealing with a numeric, excel will always store it in teh smallest possible way...
0.0045000 will become 0.0045
0.00450001 will become 0..00450001
0..0045100 will become 0..00451
I dont know of any way to force excel to store extra zeros (that it does not need) in a cell.
I understand that you need to extend your calcs to the lowest relevant decimal place, but why do you feel the need to store extraneous zeros? excel will not use them, nor does it need them - but the cell can be formated to display them
edit: is there an echo in here, Ron...in here Ron...ere Ron...on........
Last edited by FDibbins; 09-02-2015 at 02:02 PM.
I don't think that there is a way to keep the number as a number and retain those trailing zeros. The trailing zeros at the end of a decimal value do not change the value in any way mathematically in any way as far as I have been able to determine.
I don't see what is possibly gained by showing zeros to the right of the decimal if they have no mathematical value. All you can possibly get is an appearance. If you knew how many digits the value has then a format could possibly be created to give the zeros but that would only be a presentation (something that you see).
Last edited by newdoverman; 09-02-2015 at 01:56 PM.
I'm facing the same issue and these trailing zeros does matter as the data is part number and vendors doesn't recognize it if the trailing zeros are not mentioned correctly. This data has sometimes no periods and I can't use the number format and specify zeros after the decimal place as it will change the part number e.g. if the part number is 1234 then it will convert to 1234.0000. if the part number is 2345.010 then it will convert to 2345.0100. below are the few examples of the mixed data I have
12456
9234.0100
9235.070
94560
I need the data to be converted into number as it is mentioned in text format exactly without loosing the trailing zeros
Thanks
Anees
Hi Anees & Welcome to the Forum,
Unfortunately your post does not comply with Rule 2 of our Forum RULES.
Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
HTH
Regards, Jeff
Hi,
how to post new thread?
I found that post relevant to my query that's why posted on the same thread
We understand you found this post relevant, but post #10 does a good job explaining why you need a new post.
For posting a new thread.
https://www.excelforum.com/faq.php?f...eading_posting
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks