Set a cell to a null value

  • Thread starter Thread starter JF
  • Start date Start date
J

JF

I have a formula that check to see if the adjacent cell is blank (using
the ISBLANK function).

How do I set the valu of the current cell to a null value if the
adjacent cell is balnk.

I currently have in B1:

=if(ISBLANK(A1),"",TEXTX1)

Although A1 is blank it appears B1 is blank. However, when I save the
file as a .CSV (comma seperated value) it does not see the cell as a
blank.

If I have the formula in cells b1 - b10 (checking a1-a10) and rows 8,9,
10 are "blank", then when I save the file to the .csv file, I only want
7 rows of data but I end up with 7 rows of actual data and then 3 rows
of just comma's.

I even tried to do a copy and paste special/values but get the same
results.

Any Ideas?

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi
you can't return a BLANK cell from your IF function. Excel does not
support a 'Blank' return in this case
 
It might be easiest to just paste your values into a new worksheet in a new
workbook. (Just 7 rows.)

then save from there.

Or maybe you could use a macro to write exactly what you want.

here are some samples:

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

But try Earl's first--it may work exactly the way you want and you won't need to
write any code.
 
Hi
you may try the following macro

Sub Delete_empty_cells()
application.screenupdating = false
With ActiveSheet.UsedRange
Set c = .Find("", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
application.screenupdating = true
End Sub
 
You could do it in a macro, but try:

Selecting your range
data|filter|autofilter
use the dropdown in A1
and filter on Blanks
Delete those visible cells.
 
Thanks to all for your responses. I was able to use the one response
from Frank and also part of code found from one of the other repsponses.
That code is as follows:



Public Sub CharacterSV()
Const DELIMITER As String = "|"
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub

This code actually made the output that I needed without deleting the
"blank" lines. It did not export the "blank" lines .

Again, thanks to all!!!

JF


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Back
Top