Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Can a formula read US$1500 as a number? (2002-XP)

    Hello, it's time for my annual Excel question.

    I was sent a workbook which was created by importing a print-capture of an accounting report. I needed to sum four columns of numbers, in two different formats:

    $25,000.00
    US$1,500.00

    I couldn't think of a clean way to do this, so to get the quick and dirty totals I needed this morning, I used Edit>Replace to get rid of the US$ characters from the range I was working on.

    I still need to produce a nice end product, and my goal is to leave the original data unchanged. Is there some way to SUM this formatted text, some way to tell Excel just to ignore the US$ part?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can a formula read US$1500 as a number? (2002-XP)

    Say the US$ amounts are in A1:A50. The following array formula (confirmed with Ctrl+Shift+Enter) will sum the amounts:
    <code>
    =SUM(1*SUBSTITUTE(A1:A50,"US$",""))
    </code>
    (You can probably use "US" instead of "US$")

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Can a formula read US$1500 as a number? (2002-

    That's really cool. And yes, Excel did handle the text values with $ just fine.

Posting Permissions

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