sparky2205
Active Member
- Joined
- Feb 6, 2013
- Messages
- 481
- Office Version
- 365
- 2016
- Platform
- Windows
My apologies if this question has been posed elsewhere. I have been trawling looking for an answer but all I can find are partial solutions.
My issue:
I want to convert a number to text and display all trailing zeros.
Simple enough you might say.
Here's the catch. The number to be converted, let's say 15.40, isn't actually 15.40. It was entered into the cell as 15.40 but of course Excel chopped off the trailing zero and it became 15.4. Then the cell was formatted to display 15.40 again. Of course Excel still sees this number as 15.4.
My reason for wanting this:
I have a column containing a mixture of nearly anything, numbers and text. Numbers can be with or without decimals, various lengths, contain various numbers of leading and trailing zeros etc.
Using a formula I have to transfer each of these values to another worksheet. The formula is using INDEX MATCH MATCH to obtain the values then converting the values to text. Using IF statements to identify integers and with a mix of =TEXT(cell,0) and =TEXT(cell,"0.######") I can satisfy all the criteria bar the trailing zeros. "0.00" won't work as this fills to exactly 2 decimal places. "0.000000" won't work as this fills to exactly 6 decimal places etc. I need the number of decimal places to be variable.
Is there a number format somewhere that I am missing?
Is there some way of forcing Excel to see the displayed number in a cell as the actual value of the cell?
I hope this is clear. Any help much appreciated.
Regards,
Joe.
My issue:
I want to convert a number to text and display all trailing zeros.
Simple enough you might say.
Here's the catch. The number to be converted, let's say 15.40, isn't actually 15.40. It was entered into the cell as 15.40 but of course Excel chopped off the trailing zero and it became 15.4. Then the cell was formatted to display 15.40 again. Of course Excel still sees this number as 15.4.
My reason for wanting this:
I have a column containing a mixture of nearly anything, numbers and text. Numbers can be with or without decimals, various lengths, contain various numbers of leading and trailing zeros etc.
Using a formula I have to transfer each of these values to another worksheet. The formula is using INDEX MATCH MATCH to obtain the values then converting the values to text. Using IF statements to identify integers and with a mix of =TEXT(cell,0) and =TEXT(cell,"0.######") I can satisfy all the criteria bar the trailing zeros. "0.00" won't work as this fills to exactly 2 decimal places. "0.000000" won't work as this fills to exactly 6 decimal places etc. I need the number of decimal places to be variable.
Is there a number format somewhere that I am missing?
Is there some way of forcing Excel to see the displayed number in a cell as the actual value of the cell?
I hope this is clear. Any help much appreciated.
Regards,
Joe.