Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Empty not really empty (2003)

    I am trying to use a field in a calculation but there appears to be something in the blank fields. I am using the following: If(A4="","Benefits Administrator",A4). I tried is Null too. This excel file comes from a program called Cognos 8. Apparently, when you save the report (from this program) to Excel it brings in extraneous data. Is there something I can use in the if statement that would handle this?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Empty not really empty (2003)

    Hi Linda

    In an empty cell type =code(A4), does it return a value and if so, what?
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty not really empty (2003)

    It returns 51

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Empty not really empty (2003)

    Well that shows there is a character in the cell!! I have seen this problem before when importing to excel.

    Weird I use Cognos and I have never had this problem before as the value 51 corresponds to the ASCII character 3 <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    1) Is there anyway you can have Cognos extract your data as CSV format and then import this to Excel, it may strip out the extraneous characters.

    2) Is the extract tabular data that you can view it in a text file and see if there are any characters visible?
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty not really empty (2003)

    I just thought there was something I could do (like a macro) to remove anything from a cell that looks empty but isn't.

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

    Re: Empty not really empty (2003)

    Code has been posted in this forum (several times) that clears cells that look empty but aren't. But such code wouldn't help, for as Jerry noted, 51 is the ASCII code for the digit 3, which is not empty at all. Are you sure the cell hasn't been formatted to look empty, for example by using white as font color?

    If you wish, you can attach a sample workbook.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Empty not really empty (2003)

    I guess I am so exasperated with Cognos 8 saving to Excel. I am attaching part of the spreadsheet with the column that has empty cells.
    Attached Files Attached Files

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

    Re: Empty not really empty (2003)

    The empty-seeming cells don't contain ASCII code 51 - I have no idea where you got that from. They contain non-breaking spaces (ASCII 160). Try this macro:

    Sub ClearEmpty()
    Dim oCell As Range
    On Error GoTo Einde
    Application.ScreenUpdating = False
    For Each oCell In Selection
    oCell.Value = Replace(oCell.Value, Chr(160), " ")
    If Trim$(oCell.Value) = "" Then
    oCell.ClearContents
    End If
    Next oCell
    Einde:
    Application.ScreenUpdating = True
    End Sub

Posting Permissions

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