excel insert null into empty cells

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for the quick reply although it doesn't seem to be working. When I have all the blank cells highlighted there is one cell selected (not by me) and that cell gets the value(0) entered and nothing happens to the rest of the highlighted blank cells.

Yes the zero only goes in that one but you do not confirm that by Enter or Tab but by Ctrl + Enter and it will insert the value in all that is selected. Right after you write 0 you press Ctrl+Enter!

Good Luck

ASKER

I'm sorry but when I press Ctrl+Enter nothing at all happens.

Hello,

what do you actually want to show in the cells? What do you mean by "null value"? A zero, as in 0??

You could enter a non-breaking space, which, unlike a regular space, will not be ignored in html, but will also not show a character just for the character's sake.

The method described above to select all blank cells is a good way to go, but instead of entering a 0, I'd go for

=char(160)

which will insert the equivalent of   into the cell. So, step by step:

- select all cells in the sheet (or the range you want to use)
- hit F5
- Click Special
- Tick "Blanks" and hit OK
- start typing and enter =char(160)
- hold down the Ctrl key and hit Enter

Now all the previously empty cells will contain the non-breaking space character, ANSI code 160.

cheers, teylyn

Ok, bare with me. Let's try this, Select cells A1:A10, write 0 and press Ctrl+Enter while the cell is still active! The cursor should still be visible and all the cells still selected with a zero in cell A1!. What you should get is a zero in all the cells you selected.

And the same principle applies in your situation... You select only the blank cells nad then by Ctrl+Enter enter the 0 in all of them...

Best wishes

PS: this way of inserting values is one of Excels greatest gems so the effort now will be re-payed a thousand times :)

ASKER

I'm starting to feel like an idiot, but Ctrl+Enter is not doing anything at all. I did as you suggested, created a separate worksheet, selected A1:A10, wrote 0 (see attached), pressed Ctrl+Enter and nothing happens.
excel.gif

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yeah, should work...

That screenshot shows the cell before the Enter key has been hit (or Ctrl-Enter). It shows the state of the selected cells and the 0 character entered into the first cell. The cell is still in edit mode, since the 0 value is clearly left justified. If it had been entered, it would be right-justified, unless the cells are formatted as text, which is unlikely in a new worksheet/workbook. The other indicator that the cell is in Edit mode is the fact that all the ribbon icons are greyed out. Hence, you have not hit Enter or Ctrl-Enter when the screenshot was taken.

If you select several cells, enter anything and hold down the Control key while you press the Enter key, the value or formula will be entered in all the selected cells. You need to HOLD DOWN the Control key (it's the one at the very far left or right of the space key) and while you hold it down, press the Enter key.

ASKER

I may be feeling like an idiot right now, but I do know where the Control key is...

ASKER

Thanks for your help. The F2 and little bit more patience on my part seemed to do the trick.

On the same row as the space key, at the very left and again on the very right. Just below the Shift key.

It normally has the letters

ctrl

written on it.

If you're working on a PC, that is. If you're on a Mac, check out http://newmacuser.com/apple-keyboard-symbols/

cheers, teylyn

You could have spread the love a bit wider, especially with all the help required to find the Ctrl key.