Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Black Earth, Wisconsin, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert decimal point (Excel 97)

    I've tried the HELP in Excel and tried searching the Lounge. I haven't had success. Maybe I am not wording the search correctly. I have a column of numbers. Example:
    2210
    2210
    1100
    1100
    480
    480
    1110
    1140
    2210
    These numbers are actually dollars amounts that need to be formatted as follows:
    $22.10
    $22.10
    $11.00
    $11.00
    $ 4.80
    $ 4.80
    $11.10
    $11.40
    $22.10

    I have tried formatting as currency and custom formatting but I end up with a decimal point at the right side and more zeros to the right. Hopefully someone may have a "simple" solution as I'm not into writing code. Thanks for looking.
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert decimal point (Excel 97)

    A custom format of $0"."00 will do the trick. You have to put the decimal point in quotes so XL knows that it's just text. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insert decimal point (Excel 97)

    Hi,
    If you only want to format them, then following Sam's suggestions will do it. If you actually wanted to convert them, enter 0.01 in an empty cell, copy it, select your column of numbers and choose Edit-Paste Special and check the multiply box. This will divide all your numbers by 100 and you can then format them as currency.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Location
    Black Earth, Wisconsin, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert decimal point (Excel 97)

    Thank you, Thank you, Thank you.
    I was sure there was a way to do it but with very little knowledge of formulas and formatting I was at a loss. You have saved me hours of experimenting. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert decimal point (Excel 97)

    > wanted to convert them
    Rory, that is too cool! Even works for formulas by changing the formula! I learned something today, guess I can go home! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insert decimal point (Excel 97)

    No fair - I'm working at home, so does that mean I have to go to the office?? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert decimal point (Excel 97)

    Only if you need the rest! <img src=/S/snore.gif border=0 alt=snore width=32 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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