When is a blank cell not a blank cell

[warriorwoman]

I have a load of what I consider to be blank cells but excel isblank() function returns the value false.
There are no spaces in the cell but if I place the cursor in the cell then press F2 to edit and then enter the isblank() function returns True.

Can anyone explain?

I am trying to write a macro that will overwrite the values in column A where the values in column C = "CQUIN" or "U CODE". I was going to handle this in a clunky fashion by:

1. creating in new column D with the formula =IF(OR(C3="CQUIN","U CODE"),C3,"")

2. copy then paste as values column D - thereby hopefully leaving myself with a load of blank cells and ones with CQUIN or U Code in.

3. then selecting column D and paste special as values - skipping blanks over column A

Unfortunately this isn't working as it doesn't regnise the blank cells.

 

[Jon von der Heyden]

Hello

That cell is no longer considered blank because it was occupied by a formula that returned "". To fix this, after you have copied over a values, select the column and invoke Text to Columns (in Data menu or Data tab depening on your XL version).

In Text to Columns Wizard choose Delimited in step1.
In step 2 clear all delimiters
Then hit finish.
To check that the blanks are now indeed blank, with the same column selected, hit F5 > Special > Blanks > Ok. Only those blank cells should remain selected.

The selection should now be ready for paste (with skip blanks checked).

 

[warriorwoman]

Thank you very much, that did the trick.
Never used Text to Columns before.

 

[Jon von der Heyden]

Looking back at your formula I think you should be using:

=IF(OR(C3={"CQUIN","U CODE"}),C3,"")

But I think you can simplify by one step with:

=IF(OR(C3={"CQUIN","U CODE"}),C3,NA())

Next, select column D.
Hit F5 > Special > Formulas and leave only Errors checked
This should leave only the #N/A's selected.
Hit the Delete key.

Now select the data in column D again > copy, go across to column A, paste special > Values + Skip Blanks.

 

[warriorwoman]

The curly brackets don't work for me but the NA() trick is probably a time saver.

Thanks

 

[Jon von der Heyden]

Strange that the curly brackets don't work. What do you mean when you say they don't work? Does it pop up with an error or not return the expected result?

Alternatively:

=IF(OR(C3="CQUIN",C3="U CODE"),C3,NA())

But this construct won't return correct results:
=IF(OR(C3="CQUIN","U CODE"),C3,NA())
...as "U CODE" is not being considered as part of the OR evaluation against C3.

 

[Jon von der Heyden]

This illustrates the results of the different ways. I'm afraid I don't see how your initial method could work:

Excel Workbook
	C	D	E	F
1	VALS	Formula1	Formula2	Formula3
2	CQUIN	CQUIN	CQUIN	#VALUE!
3	U CODE	U CODE	U CODE	#VALUE!
4	SOMETHING ELSE	#N/A	#N/A	#VALUE!
sheet

Excel 2003

Cell Formulas
Range	Formula
D2	=IF(OR(C2={"CQUIN","U CODE"}),C2,NA())
E2	=IF(OR(C2="CQUIN",C2="U CODE"),C2,NA())
F2	=IF(OR(C2="CQUIN","U CODE"),C2,"")

 

[warriorwoman]

Oh I'm sorry I wrote it out incorrectly in the first example, I was actually using the following expression

=IF(OR(C3="CQUIN",C3="U CODE"),C3,"")

I'll retry the curly brackets expression as you've clearly got it working ok in your example below.

Thanks for your time.

 

[SaladProblems]

If it's something you're going to be doing repeatedly in the long run, would using LEN()=0 be more helpful than ISBLANK()?

That'll return a true on cells that are filled with "" unlike ISBLANK