Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts

    Need help with format

    I apologize for what is probably a simple problem, but I am stumped and so I'm asking for help.
    I have a spreadsheet that uses an XLA function to retrieve financial information from sites like Microsoft and Yahoo.

    One of my columns of data is retrieiving sales data, and for a symbol like IBM used to return, in currency,
    something like $81,000,000,000. Now it comes back as $81 Bill

    Later calculations in my workbook depend on a numeric answer, and blows up with the $81 Bill response.

    I have Excel 2007. I'd appreciate help with getting back to the $81,000,000,000 rather than the $81 Bill.

    Dick

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    I'm replying to my own post, because I need to reframe my question.
    I checked the MSN financial site that the data is drawn from, and they have changed how they report the field
    I'm interested in.
    So, my question now is: if the data field says $81.7 Bill, how can I massage it to be a numeric field of
    just 81.7?
    Thanks,
    Dick

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    You need to either change the XLA or modify the data in the spreadsheet.
    Are you able to modify the XLA?
    How many of these values do you have?
    You could change the formula that fails, but this may not work if the formula is a SUM.

    cheers, Paul

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If all your values are all of that sort, you can use something like (adjust the cell as desired):
    =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"$","")," Bill",""))

    If they have different formats, it may need to be adjusted.
    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    Dick-Y (2012-04-08)

  6. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Thank you both for responding so quickly. Steve, your approach works beautifully for me.
    Paul, I can't change the XLA because it's part of a commercial product that I use in my own
    financial spreadsheets.

    best,
    Dick

Posting Permissions

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