Fill in Blank Cells (and cells that look blank that are not)

[pcjjjr]

I imported some data and had several extra spaces in each cell.

I used the trim command in a new sheet, and then copy and paste values for
the data that I want to work with.

I then use the vlookup to reference this data on another sheet. I then try
to subtract the two numbers, and if the cell is "blank", I get the #VALUE!
error.

If I F5/Special/Blank, these cells are not highlighted for input (as if they
are not blank). Is there any way that I can get these cells to contain a
0(Zero), so that I can perform the calculations without error?

 

[Pete_UK]

If you imported the data from a website, you may have the non-breaking
space character (160) in the cells - TRIM does not remove this. You
can use Find/Replace (CTRL-H) to remove them. Highlight the cells then
CTRL-H:

Find What: Alt-0160
Replace with: leave blank

Click Replace All.

Note for Alt-0160 you need to hold down the Alt key and type 0160 from
the numeric keypad.

Now if you do F5|Special|Blanks you should be able to highlight the
cells which appear empty - type 0 and then do CTRL-Enter to fill these
with zero.

Hope this helps.

Pete

 

[MartinW]

Hi,

It may be the non-breaking space (or hard space) problem.
One way to fix that is to use find and replace.
Edit>Replace
Find what: Alt+0160
Replace with: leave this blank
Click Replace All

To use unicode, hold down the Alt key, type the numbers
on the Number pad only, let go the Alt key.

To find out more about non-breaking spaces try a
google search.

HTH
Martin

 

[pcjjjr]

This worked perfectly. It was so hard for me to troubleshoot this issue
since I couldn't find anything in the cell, yet it wasn't "empty".

I had imported from another application (not a website), but the character
was there, creating the error.

Thanks again for your help and the quick response!!!

 

[pcjjjr]

Same as above:

This worked perfectly. It was so hard for me to troubleshoot this issue
since I couldn't find anything in the cell, yet it wasn't "empty".

I had imported from another application (not a website), but the character
was there, creating the error.

Thanks again for your help and the quick response!!!

 

[Pete_UK]

Thanks for feeding back - glad you solved the problem.

You could check for a character being in a cell that looks empty by
means of the formula:

=LEN(A1)

which will tell you how many characters are actually there, and the
following formula:

=CODE(A1)

will tell you what the character code is of the first (or only)
character.

Hope this helps.

Pete