Blank but not empty cells

G

Guest

Guest
Hi,
I have imported data from an external file into excel. Some of the cells seems to be blank but obviously not empty!!
They have length (4) and they do not "behave" as empty cells.
How can I transform them to blanks?
Eli
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Why arethey obviously not empty?
If they seem to be blank. Have you considered that the text may be coloured white?
And if they don't behave as empty cells, how do they behave?
 
Upvote 0
On 2002-03-10 05:15, waggy30 wrote:
Why arethey obviously not empty?
If they seem to be blank. Have you considered that the text may be coloured white?
And if they don't behave as empty cells, how do they behave?
1) when I want to mark all empty cells - they are not marked
2) they have length =len(a1) gives 4
Eli
This message was edited by eliW on 2002-03-10 05:23
 
Upvote 0
Here's some code which will get rid of any cells which only contain space characters. I've used an array for purposes of speed and it seems to run OK. Did a 2000x15 test in 0.3 seconds.

Let me know how you get on,
D

Code:
Option Explicit

Sub ClearSpaces()
Dim rngeCells As Range, vArray() As Variant
Dim lngCol As Long, lngRow As Long

Set rngeCells = ActiveSheet.UsedRange

ReDim vArray(1 To rngeCells.Rows.Count, 1 To rngeCells.Columns.Count)
vArray = rngeCells.Value

For lngRow = 1 To rngeCells.Rows.Count
    For lngCol = 1 To rngeCells.Columns.Count
        If SpacesOnly(vArray(lngRow, lngCol)) Then
            vArray(lngRow, lngCol) = vbNullChar
        End If
    Next lngCol
Next lngRow

rngeCells.Value = vArray

End Sub


Function SpacesOnly(vValue As Variant) As Boolean
'Returns true if vValue only contains spaces
Dim lngCharloop As Long

SpacesOnly = True
For lngCharloop = 1 To Len(vValue)
    If Asc(Mid$(vValue, lngCharloop, 1)) <> 32 Then SpacesOnly = False: Exit Function
Next lngCharloop
End Function
 
Upvote 0
Thank you D
I prefer to deal with it with formula rather then VBA code, like for example:
if(len(a1)>=4,"",a1)
Thatsolved my problem, yet I do not understand what is the problem and how can a cell contain something and be invisible
Eli
 
Upvote 0
If the cells have a length of 4 but they look blank then I'd imagine that the cell has 4 space characters in it. To get rid of cells like that with a formula is going to be tricky. In your example if(len(a1)>=4,"",a1) that would return "" for anything even the number 1234 or word Excel. Maybe I'm missing something here.

Regards,
D
 
Upvote 0
On 2002-03-10 05:41, eliW wrote:
Thank you D
I prefer to deal with it with formula rather then VBA code, like for example:
if(len(a1)>=4,"",a1)
Thatsolved my problem, yet I do not understand what is the problem and how can a cell contain something and be invisible
Eli

Eli,

Text to Columns would eliminate spaces.

You can check what char(s) such cells contain:

=CODE(LEFT(A1))
=CODE(LEFT(A1,2)

etc.

Aladin
 
Upvote 0
Hi
Could you confirm they are actually 4 spaces by using the find function? (In the find-what box just press the space bar 4 times).
If so you could do use replace function (leave the replace with what box blank)
Just a thought
regards
Derek
PS Trim formula might also do it
This message was edited by Derek on 2002-03-10 06:07
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top