Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Difference between a blank and a blank cell... (EXCEL 97/2000)

    Please check out the attached little sheet (there's no VBA, just one sheet with only a few cells).

    You'll find two empty cells: a green and a red one. The length (LEN()-function) for both is zero, yet the ISBLANK() function says the red-cell is NOT blank. This is because a formula was created which looked something like: =IF( ISERR(ThisCell), "", ThisCell)

    Next the cell with this formula was copied and pasted-as-value (ergo: in the cell there is an empty string "")...

    So far so good; now if we'd have a range of data and make a LINE plot (not XY), I do not want to plot these empty cells (just want to have gaps there).

    Here's the problem: if the cell would be really empty (like the green cell), indeed a gap would be plotted. The "empty" cell created via the formula however is NOT really empty (as proven also by the BLANK() function) and thus plots as a ZERO...

    Question: how does one reformulate the above formula to make sure that in the copied range, cells that had errors are really empty (and NOT "")???

    I thought/hoped there would be a BLANK() function but there isn't. The NA() function doesn't work (it does for an XY-plot, I use that all the time). Chart-options to 'leave-a-gap' also don't work as the cell isn't really empty...

    Erik Jan
    Attached Files Attached Files

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

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    Erik Jan,

    This is not what you asked, but you may be able to use it.
    The following macro by Legare Coleman (from <post#=136921>post 136921</post#>) will clear all cells in the selected range whose value is a string consisting of zero or more spaces. (This also applies to formulas that evaluate to a string consisting of zero or more spaces!)

    Sub DeleteEmptyStrings()
    Dim aCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each aCell In Selection
    If Trim(aCell.Value) = "" Then aCell.ClearContents
    Next aCell
    On Error GoTo 0
    Application.ScreenUpdating = True
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    Sorry for the delay... anything out there without VBA??

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    The only way is to clear the cells that look clear but aren't <img src=/S/smile.gif border=0 alt=smile width=15 height=15>:

    - select the cells that seem clear
    - goto, special, constants (just check the Text option button), OK
    - hit the Del key.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    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

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    On a chart (whether an XY or a line):
    If the "dependent variable = Y-Value" is a number That number will be plotted
    If the "dependent variable = Y-Value" is text a zero will be plotted
    If the "dependent variable = Y-Value" is =na() it is ignored and the point before/after will be connected
    If the "dependent variable = Y-Value" is any other error a zero will be plotted
    If the "dependent variable = Y-Value" is BLANK then nothing will be plotted at that point (the line will be broken)

    A formula, no matter WHAT the result is, can NEVER be BLANK, (the cell has a formula in it!)
    A NULL string ("") is also NOT Blank (it is a string) and will plot as a zero.
    The only way a cell can be BLANK, is if there is NOTHING in that cell.
    Steve

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    This is a recurrent problem between the users and here we have mentioned it in several occasions

    The following solution only works for a single column

    Menu
    1. Select your ranges of data (e.g. Col D)
    2. Menu "Data, Text to Columns

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    All: thanks for the replies & suggestions... to limit the 'solution space' let me repeat that I'm not looking for any VBA cure nor (!!) for something I need to do manually... ergo I'm looking for an EXCEL-type (formula) solution (see my original email).

    I will try to create a clearer example including a plot, describing the problem better...

  8. #8
    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

    Re: Difference between a blank and a blank cell... (EXCEL 97/2000)

    As I mentioned in my post, the ONLY way to leave a break in the line is to LEAVE the space blank. This can NOT be done with a formula: a formula in a cell is NOT blank, and CAN NOT be made blank. A blank cell is EMPTY.

    You can thus either leave the cells blank or if you use a formula, you must turn it into something and then MANUALLY or with a MACRO, DELETE that something to make the cell EMPTY.

    Using "NA()" will NOT plot the point, but will continue the line.

    Steve

Posting Permissions

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