Results 1 to 11 of 11

Thread: Cell Formats

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have tried and tried to format the "text" cells in column A to numbers but to no avail; there appears to be inconsistencies from some blank cells to others. I want them all to be formatted as numbers. Any ideas.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The seemingly blank cells that Excel sees as text do in fact contain text: one or more spaces. If you clear those cells, Excel will see them as numbers too.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='800011' date='26-Oct-2009 21:29']The seemingly blank cells that Excel sees as text do in fact contain text: one or more spaces. If you clear those cells, Excel will see them as numbers too.[/quote]

    This can happen if people use space not delete to clear cells.
    Andrew

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800011' date='26-Oct-2009 16:29']The seemingly blank cells that Excel sees as text do in fact contain text: one or more spaces. If you clear those cells, Excel will see them as numbers too.[/quote]


    Thanks Hans. How do I delete these spaces so that the formulas in column B will read "Number" for the empty cells in column A. I have tried the ASAP utility, but to no avail...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I use the following macro to clear all blank-seeming cells:

    Code:
    Sub ClearBlankCells()
      Dim oCell As Range
      On Error GoTo ExitHere
      Application.ScreenUpdating = False
      For Each oCell In Selection
    	If Trim$(oCell.Value) = "" Then
    	  oCell.ClearContents
    	End If
      Next oCell
    ExitHere:
      Application.ScreenUpdating = True
    End Sub
    Select a range of cells, then run the macro.

    Warning: do NOT include cells with formulas in the selection. Formulas that result in a blank would be removed.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hans,
    I tried your macro, but the column B cells indicating "Text" do not change as I had hoped. The problem is that I need for them to be empty so that when I when I run a GoTo Search Special and select 0, I can perform a fill-down as per a previous discussion on this forum. In short, after I run the delete text function from ASP, I need all of the cells in column A to be either numbers or empty, but read as numbers, not text.
    Any ideas?
    Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yuk! The cells also contain line feeds! How did you end up with this rubbish?

    Try this version:

    Code:
    Sub ClearBlankCells()
      Dim oCell As Range
      On Error GoTo ExitHere
      Application.ScreenUpdating = False
      For Each oCell In Selection
    	If Trim(Replace(oCell.Value, vbLf, "")) = "" Then
    	  oCell.ClearContents
    	End If
      Next oCell
    ExitHere:
      Application.ScreenUpdating = True
    End Sub

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, that did the trick. For my future reference, what was it that we got rid of that we couldn't see (and how do you see it?)
    Thanks again.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The cells contained "line feed" characters - ASCII code 10. You can enter these in a cell by pressing Alt+Enter. If you turn on "Wrap text" in the Alignment tab of Format | Cells..., these characters will act as line breaks.

    I found this out by entering the formula

    =CODE(A1)

    in C1 and filling down. The formulas returned 10 for several of the "blank" cells.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800029' date='26-Oct-2009 17:12']The cells contained "line feed" characters - ASCII code 10. You can enter these in a cell by pressing Alt+Enter. If you turn on "Wrap text" in the Alignment tab of Format | Cells..., these characters will act as line breaks.

    I found this out by entering the formula

    =CODE(A1)

    in C1 and filling down. The formulas returned 10 for several of the "blank" cells.[/quote]

    How can you delete them other than by your code? Anything in ASAP?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use formulas with the CLEAN function, but then you'd still run into problems with number vs text. The macro is much more convenient. Even better would be to avoid entering only spaces and line feeds in a cell.

Posting Permissions

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