+ Reply to Thread
Results 1 to 3 of 3

Extracting cents, dollars, etc from amounts

Hybrid View

  1. #1
    Registered User
    Join Date
    07-16-2010
    Location
    Riverdale, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Extracting cents, dollars, etc from amounts

    This is probably a fairly easy question, but it currently has me stumped.

    I have a form adding up a list of totals (in dollars, currency is set to #.## format). I was trying to extract each individual place at will. Say for example:

    14.52

    I want it to be able to put the 1, 4, 5, and 2 in separate cells I designate. At the moment, I'm using the Left, Right, and Len functions to do this. There is only one problem.

    If every amount ends in .00 (even dollar amounts like 3.00, 4.00, 5.00, etc.) the format on the total is still set to add the .00 to the end. The problem is, when I try to use these functions on that type of number, it... doesn't "see" the .00 that is automatically added on. For example, the number 456.00. If I extract the first number on the right in that using an Excel formula (which should be 0), I get 6. Can anyone help with accounting for this? How would I go about extracting the specific numbers in specific locations?

    Thanks for the help!

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Extracting cents, dollars, etc from amounts

    With
    A1: containing a dollar value (max $999.99) example: 123.45
    And
    B1: Hundreds
    B2: Tens
    B3: Ones
    B4: Dimes
    B5: Pennies

    This formula, copied down through C5, returns those values:
    C1: =MID(TEXT($A$1*100,"00000"),ROWS($1:1),1)
    In the above example the formulas return:
    1
    2
    3
    4
    5

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    07-16-2010
    Location
    Riverdale, Georgia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Extracting cents, dollars, etc from amounts

    Thank you very much, that's exactly what I needed; I knew it was something that I was overlooking!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1