Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Number Formatting (97)

    Hi all,

    Is it possible to format a number based on the value of another cell? no VBA
    example:
    When A1 contains a 0 A2 shows 2,345
    When A1 conatins a 3 A2 shows 2,345.000

    TIA
    Joop

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Formatting (97)

    No, you would have to use VBA to do this. If you want to use VBA, we can show you how.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Number Formatting (97)

    Thanks Legare, for the fast response.
    I know about the vas amount of knowledge in this Lounge.

    Thanks again , Joop

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number Formatting (97)

    Depending upon what you want to do with the values stored in the resulting cells, there may be a way. This technique uses the TEXT formula to apply the correct formatting. Numbers which have been converted to TEXT can cause problems in subsequent calculations if they are not converted back to numbers. You can use the VALUE command to this, either on the fly, or by storing elsewhere the numeric equivalent. Only you can decide whether the processing overhead warrants this approach. Anyhow..

    =TEXT(D4,"0,000"&IF(C4>0,"."&REPT("0",C4),""))

    Where:

    D4 = Is the D4 the actual number you want to re-format. This was 2,345 in your example.
    C4 = The number of places AFTER the decimal point you want to show.

    The attached spreadsheet has a worked example. Hope this may be of some use.

    Regards
    Peter

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Vlissingen, Netherlands
    Posts
    63
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Number Formatting (97)

    Thanks Peter,

    Will see if i can benefit from your idea.

    Rgds,
    joop

Posting Permissions

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