Can you post the text that you start with, and tell us how you convert
it to a number? This will help with a solution. If you have an entry
such as '0012345 (note the leading apostrophe in the entry) then Excel
will treat that as text. You may be able to bypass the text to numeric
conversion with a formula such as =VALUE(A1) where A1 contains the
'0012345. That way you could use the converted value without losing
the text entry.

Also, what do you mean by "I'm aware the last number will change to 0"?