Invisible text

[jbvinny]

Used the left formula on a list (10,000 rows) of address. Copy and pasted as values when i was done. Some of the rows do not have a value. However, excel reads it as such even though nothing shows up in the cells. If i click on the cell it clears whatever invisible data there is. Anyone have this problem and an easy solution to clear 3,000 plus lines without clicking in each one?

 

[KGee]

Is it returning text or just spaces? If spaces, you can try a find and replace with "nothing".

 

[jbvinny]

it doesnt read the cell as a space or anything. Find and replace does not work. Its really wierd and I have never seen it before. There is literally nothing in the cell but formulas read it as such.

 

[Willem!]

...perhaps you choose 'paste special -> formulas'?

...save, close and open your file once...

...force a total re-calculation: press Ctrl-Alt-F9or Alt+Shift+Ctrl+F9

HTH

 

[jbvinny]

I didnt paste the formulas either...tried that too. I dont not have to delete anything in the cell for it to work. If i just double click on the referenced cell it will then work in the formula. Its really wierd.

 

[KGee]

Strange. Try taking a look at one of the original cells that returns the strange characters when you use your formula. Maybe there is some sort of gibberish or non-printing characters in the original address.

 

[lenze]

Maybe redo your formula

IF(LEN(A1)>0,LEFT(A1,5)+0,"")

lenze

 

[texasalynn]

or maybe your automatic calculations are set to manual? Check Tools - Options - Calculations, is it checked automatic?

 

[jbvinny]

Calculation is set to automatic. I have even tried the clean, trim and other functions without much luck. I did find a work around. The whole thing just seemed odd.

 

[pgc01]

Hi

Are you sure the cells are really empty?

Try:

=ISBLANK(A1)

for a cell that is "empty". If the result is not True then the cell is not empty.

Maybe it has an empty string?

Try:

=ISTEXT(A1)
=Len(A1)

if the result is True and Len is zero, then there's an empty string in the cell.