+ Reply to Thread
Results 1 to 7 of 7

Remove multiple #VALUE!

  1. #1
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Remove multiple #VALUE!

    Hi,

    I have a spreadsheet where I have a formula entered into a cell and dragged down over 4900+ cells. The formula is =LEFT(E2,LEN(E2)-4) effectively it removes the last 4 characters from the cells in the row next to it.

    Now not all of the cells used in the calculation contain data, some are empty. When this formula tries to do the calculation on an empty it returns "#VALUE!" in the output cell. Normally I can just click on the cell and delete the formula for that specific one but when I am dealing with 4900+ cells and at least 20% contain an error this is a VERY time consuming process. I have included an example with only 5 or 6 fields filled out, but image it on a much larger scale.


    example.xls


    Cheers,

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Remove multiple #VALUE!

    Hi try this
    Please Login or Register  to view this content.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Re: Remove multiple #VALUE!

    Hi tom,

    Thanks for the reply, the only problem is that I am very new to excel programming and VBA. I am not really sure how to apply a fix like this. My knowledge of excel programming goes as far as being able to write a formula in a cell and apply it. I am very much a 'newb' at VBA and macros so if you could just help me out as to how I apply this code that would be appreciated.

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Remove multiple #VALUE!

    Instead of clearing the errors, why not just add an if statement to check if the cell is blank first:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You will notice I add -- before the Left function to convert the result to a number (as in your example). If it's not going to be a number then remove the double negative signs.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: Remove multiple #VALUE!

    Try this formula:

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Remove multiple #VALUE!

    Ok so You can make the same manually
    1.select your range
    2. press F5
    3. choose "special"
    4.choose "formulas" and "Errors"
    5. press ok
    6. press delete
    and voila!!

  7. #7
    Registered User
    Join Date
    07-02-2012
    Location
    Western Australia
    MS-Off Ver
    Office 2010
    Posts
    15

    Re: Remove multiple #VALUE!

    Thank you to all that replied, in particular tom and abousetta.

    Stars to all of you. In the end I went with abousetta's solution as it seems logical to me. I am from a primarily Java and Java "Style" Syntax background and I am kicking myself for not thinking of that in the first place. Got to love a basic IF statement, I am just not used to them in excel or VBA yet.

    Problem is solved, thread closed.

    Thanks.

+ 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