Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make unwanted text disappear without VBA? (2002/XP)

    I have a workbook that gets data automatically via a webpage. However, the webpage uses an unsual format for the shipping cells. I only want to show the dollar amount in the cell. How can I remove all of the unwanted text?

    For example, I would want...

    Fedex Free Shipping to convert to a 0
    Fedex $4.00 to convert to $4.00

    I only want the dollar amount shown. And, also, I don't want to do this with any VBA. Surely there is a way to do it with formulas.

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

    Re: Make unwanted text disappear without VBA? (2002/XP)

    First a few questions:

    1- Will the dollar amount always have a dollar sign in front of it?

    2- Will there ever be a dollar sign in text that does not have a dollar amount?

    3- Will there ever be any text after the dollar amount? If so, what do you want to do with it?
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make unwanted text disappear without VBA? (2002/XP)

    The imported info will always have 1 of the 3 following formats:

    Special Free FedEx Saver Shipping
    Special FedEx Saver Shipping $X.00
    FedEx Saver Shipping $X.00

    So, basically, yes, the value I am interested in will always begin with a $ sign, unless it is free shipping.

    It does appear that there will never be any text (except for spaces maybe) following the value I am interested in.

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

    Re: Make unwanted text disappear without VBA? (2002/XP)

    For a text in cell A1, the following formula will return 0 or the amount. You'll have to set the number format to currency/financial yourself:

    =IF(ISERROR(FIND("$",A1)),0,VALUE(MID(A1,FIND("$", A1)+1,100)))

    Note: the 100 is an arbitrary value to make sure that everything to the right of the Dollar sign will be used.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    PA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make unwanted text disappear without VBA? (2002/XP)

    Holy crap, that worked! Thanks man! Next question, can you explain how that works? There's some pretty funky stuff going on in that formula that I don't understand.

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

    Re: Make unwanted text disappear without VBA? (2002/XP)

    The =IF(ISERROR(FIND("$",A1)),0 part of the formula returns a value of zero if there is no dollar sigh in the text. The VALUE(MID(A1,FIND("$",A1)+1,100))) part of the formula is what gets returned if there is a dollar sign in the text. The MID function returns everything to the right of the dollar sign and the VALUE function converts the text to a number.
    Legare Coleman

Posting Permissions

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