Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lost trailing zeros (Excel 2000)

    I am joining numbers in a string format to show a range with the number of displayed digits dependent on the values of the numbers . If a number is between 1 and 10, it is shown with 2 decimal digits eg 2.56. It is easy to do on single numbers however the final display has to be in the format: 1.23 to 3.45. If one of the numbers has a trailing zero (eg 1.20) then the zero is lost when the string is formed using formulae such as: =B3 & " to " B4 where the result is 1.2 to 3.45 rather than 1.20 to 3.45. I have tried combinations of cell formats as text but the zeros are still lost. Any suggestions would be greatly appreciated.

    Graeme C

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Lost trailing zeros (Excel 2000)

    Graeme,

    Instead of : =B3 & " to " & B4

    You need to convert the numbers to text with any format you want using the TEXT Worksheet Function as they are in a character string.

    Try this: = TEXT(B3,"0.00") & " to " & TEXT(B4,"0.00")

    I think this will solve your problem.

    Good Luck!

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lost trailing zeros (Excel 2000)

    Thanks for the advice, that worked perfectly. I hunted further and found another function FIXED(number,decimals,no_commas) which did a similar thing and had an advantage that the "decimals" item could be a variable derived from code elsewhere.

    Graeme

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •