How to make Excel recognise that the "" is blank?

P

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Hi all,

I have a formula in A1:A10 that returns either a value or a "".

The result will be copied paaste special values in another cell, B1;B10.

Then I will copy B1 and paste specia, value and SKIP BLANKS in C1:C10

Problem is that excel pastes all the cells in C1:C10 i.e. it does not consider any cell as blank although there are soe with no value (as a result of the "" in the original formula).

Is there anyway for force excel to treat those as blank in such a way that it will work when I do paste special SKIP BLANKS?

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
1) highlight the column B cells
2) Press F5 and select Special
3) Select Constants...and uncheck everything except numbers
4) Click OK
5) Press CTRL-C to copy the selected cells
6) Select C1 and press Enter


Truth is, you can do this on the original cells in column A, just change step 3 to Formulas > Numbers
 
Last edited:
Upvote 0
jbeaucaire said:
1) highlight the column B cells
2) Press F5 and select Special
3) Select Constants...and uncheck everything except numbers
4) Click OK
5) Press CTRL-C to copy the selected cells
6) Select C1 and press Enter


Truth is, you can do this on the original cells in column A, just change step 3 to Formulas > Numbers
Click to expand...

Hi jbeaucaire,

Thanks for your post.

When I pressed CTRL+C, it says THAT COMMAND CAN NOT BE USED ON MULTIPLE SELECTIONS

And besides, even if it will allow me to copy on multple selection, this will copy the ones with value and make them adjacent to each other. What I want is that they maintain the same row no when I paste them, but those without values will just be skipped.
 
Upvote 0
Hi all,

I feel I need to clarify my query:

I have the below data in cell A1:A10:

Excel Workbook
A
11
22
33-
44
55
66
77-
88
99-
1010
Sheet1


Now in B1:B10, I have this values as a result of the formula:

Excel Workbook
B
1 
2 
3-3
4 
5 
6 
7-7
8 
9-9
10 
Sheet1


Now I copied cells B1:B10 and pasted values in C1:C10:

Excel Workbook
C
1
2
3-3
4
5
6
7-7
8
9-9
10
Sheet1


Now that the figures are all constants (not formulas) I was hoping that Excel would recognise that e.g. cell C1 is blank. Apparently it doesn't because when I copy C1:C10, paste special, VALUES, SKIPBLANKS, and paste it back to cells A1:A10, the result becomes:

Excel Workbook
A
1
2
3-3
4
5
6
7-7
8
9-9
10
Sheet1


It should really be:

Excel Workbook
A
11
22
3-3
44
55
66
7-7
88
9-9
1010
Sheet1


I know that I can make the formula:

=IF(RIGHT(TRIM(A1))<>"-","",SUBSTITUTE(IF(RIGHT(TRIM(A1))="-",RIGHT(TRIM(A1))&A1,A1),"-","",2)+0)

to achieve the desired result for this particular example.

But now I am more after WHY EXCEL DOES NOT RECOGNIZE the value resulting from pasting value a "" as blank?
 
Upvote 0
Try this:

1. Select your original data in A1:A10

2. Data|Text to Columns...|Next|Next|Advanced...|tick 'trailing minus for negative numbers'|OK|Finish

(Most likely ...
Data|Text to Columns...|Finish
... will do the same job)
 
Last edited:
Upvote 0
PATSYS said:
But now I am more after WHY EXCEL DOES NOT RECOGNIZE the value resulting from pasting value a "" as blank?
Click to expand...

Hi

Before answering to your question, let's talk about a terminology error in the the excel documentation that makes this problem and others confusing: the word Blank is used with 2 different meanings.

The word Blank has 2 different meanings in the help:

1 - A Blank cell can mean an empty cell. This is the case with the function IsBlank() and the Paste Special with Skip Blanks

2 - A Blank cell can mean an empty cell or a cell with a null string. This is the case with the function CountBlank() and in the Autofilter when you select Blanks in the dropdown

Now, your present case:

You have just pasted values from cells that had formulas with the result "" (a null string) and so those cells have now null strings.

The Skip Blanks in the Paste Value is expecting empty cells and they are not empty, that's why it does not work. You have to clear the cells if you want to use the Skip Blanks in the Paste Value


There are a number of ways that you can use to clear the cells, but I guess you know them all. Examples of how to clear the cells:
- you can do it manually, just select them and press Delete
- if the other values in the range are numeric you can use GoTo to select the text values (the null strings) and press delete
- you can use the Autofilter, filter Blanks, select the filtered cells and press Delete
- you can also use code, loop through the cells, compare their value with "" and clear the ones that match
etc.
 
Last edited:
Upvote 0
pgc01 said:
Hi

Before answering to your question, let's talk about a terminology error in the the excel documentation that makes this problem and others confusing: the word Blank is used with 2 different meanings.

The word Blank has 2 different meanings in the help:

1 - A Blank cell can mean an empty cell. This is the case with the function IsBlank() and the Paste Special with Skip Blanks

2 - A Blank cell can mean an empty cell or a cell with a null string. This is the case with the function CountBlank() and in the Autofilter when you select Blanks in the dropdown

Now, your present case:

You have just pasted values from cells that had formulas with the result "" (a null string) and so those cells have now null strings.

The Skip Blanks in the Paste Value is expecting empty cells and they are not empty, that's why it does not work. You have to clear the cells if you want to use the Skip Blanks in the Paste Value


There are a number of ways that you can use to clear the cells, but I guess you know them all. Examples of how to clear the cells:
- you can do it manually, just select them and press Delete
- if the other values in the range are numeric you can use GoTo to select the text values (the null strings) and press delete
- you can use the Autofilter, filter Blanks, select the filtered cells and press Delete
- you can also use code, loop through the cells, compare their value with "" and clear the ones that match
etc.
Click to expand...

Hi PGC,

Thanks for the insights but I want to clarify that I did not paste from cells that have formula (that result to null string).

The cells that have formula that result to null string is in B1:B10

I copied paste special values B1:B10 to C1:C10.

As this point, do we agree that C1:C10 contains the empty cells?

Then I copied C1:C10 back to A1:A10, using paste special values, SKIPBLANKS and it did not SKIP the BLANKS.
 
Upvote 0
Peter_SSs said:
Try this:

1. Select your original data in A1:A10

2. Data|Text to Columns...|Next|Next|Advanced...|tick 'trailing minus for negative numbers'|OK|Finish

(Most likely ...
Data|Text to Columns...|Finish
... will do the same job)
Click to expand...

Hi Peter,

Great advice, I learned something new today.

However I am guilty of oversimplifying my example.

The actual data is actually several columns and it starts in row 6 (with headings above it and subtotals below). As such I do not think I can use the TEXT TO COLUMN trick on this one.

But thanks anyway.
 
Upvote 0
The cells that have formula that result to null string is in B1:B10

I copied paste special values B1:B10 to C1:C10.

As this point, do we agree that C1:C10 contains the empty cells?
Click to expand...
No, at that point C1:C10 aren't empty unless B1:B10 were.
 
Upvote 0
Why do these cells need to be blank, rather than contain empty strings? Perhaps you could say more about your ultimate goal - maybe all the copy and paste isn't needed.

Although, I found JBeaucaire's idea to work for me, if I used it on a column with mixed numbers and "" values that had been copied in place.

Edit - No, I was deleting the "" after pasting them in, not while pasting them...so that's different. But the use of the special cells dialog box did work for this too I guess (Control + G, then Special...)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Can you add NULL (not Blank "") to an If statement in a formula?
2
Replies
15
Views
1K
MARK858
MARK858
D
  • Solved
Excel VBA for Changing Tab Name & Return Blank Cell to Formula
Replies
5
Views
447
DjTRex2002
D
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
507
Peter_SSs
Peter_SSs
T
  • Solved
Selecting cells in the column
2
Replies
14
Views
740
MARK858
MARK858
J
  • Question Question
Removing Blank Cells - Not Working!
Replies
3
Views
515
Joe4
Joe4

Forum statistics

Threads
1,225,149
Messages
6,183,188
Members
453,151
Latest member
Lizamaison

Share this page

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