Results 1 to 5 of 5

Thread: TEXT and format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default TEXT and format

    Hi,

    I have a number in cell A1 which is 2.02. I am using the formula =TEXT(A1,"##.#")&" years" to show that I have 2.0 years.

    However, when the number is something that should be rounded to a number with a zero after it the result just says 2. years instead of 2.0 years. I don't get the zero following the number like I think I should. It works for all other values just fine.

    Don't know what I am doing wrong. I am using Excel 2010 on W7. Thx.

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXT and format

    Does something like this work?

    =FIXED(A1,1,TRUE)&" years"

    The Round() function should work...

  3. #3
    Board Regular
    Join Date
    Nov 2005
    Location
    USA
    Posts
    227
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXT and format

    Quote Originally Posted by charliec View Post
    Hi,

    I have a number in cell A1 which is 2.02. I am using the formula =TEXT(A1,"##.#")&" years" to show that I have 2.0 years.

    However, when the number is something that should be rounded to a number with a zero after it the result just says 2. years instead of 2.0 years. I don't get the zero following the number like I think I should. It works for all other values just fine.

    Don't know what I am doing wrong. I am using Excel 2010 on W7. Thx.
    Charliec, I don't understand why you're not using the format function built into Excel. You wouldn't have this problem if you had just formatted the cells as shown below.

    http://s5.photobucket.com/albums/y18...mberformat.png

  4. #4
    New Member
    Join Date
    Dec 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXT and format

    Thanks Nate. That worked!


    Quote Originally Posted by NateO View Post
    Does something like this work?

    =FIXED(A1,1,TRUE)&" years"

    The Round() function should work...

  5. #5
    New Member
    Join Date
    Dec 2010
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TEXT and format

    Thanks. I tried that but unless I did something wrong, I couldn't get it to work, but Nate's method did work.

    Quote Originally Posted by Burrgogi View Post
    Charliec, I don't understand why you're not using the format function built into Excel. You wouldn't have this problem if you had just formatted the cells as shown below.

    http://s5.photobucket.com/albums/y18...mberformat.png

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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