How can I convert a number with a decimal to a string of numbers without the decimal? It must work for all possibilities, such as 100.05000 -> 10005000, 1.23000 -> 00123, .01 ->00000000001, .10 ->00000000010, 10.00 -> 00000001000, 0 -> 00000000000. Do not want to use a macro to do so. Input value can be either in dollar or text format. Have spent considerable time on this, and help is greatly appreciated!
I'm afraid I don't follow the logic. Your numbers don't have the same number of digits in the results, so it's hard to understand exactly what you're looking for. For example, why does 100.05000 become 10005000, but 1.23000 becomes 00123? Why the leading digits in the latter and why not the last three digits?
Perhaps a simple formula of =SUBSTITUTE(A1,".","")+0 is what you're looking for. Note: If you want all the digits, you'll either have to format the result cell to the number of digits you want or take out the +0 in the formula, which will change it to text.
Last edited by darkyam; 08-21-2010 at 11:01 PM.
Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.
If you like a post, please rate it with the scales icon (top right).
If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.
Please take a few minutes to read the forum rules, and then amend your thread title accordingly.
Thanks.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
nvm. REPLACE() etcetera will do it.
thanks for the reply. this one was easy once i found replace and substitute. for whatever reason i had not come across those functions in excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks