Blank issue in the table imported from access query

[ajaar]

Hi,

I am encountering a strange issue with my data. i have a table in excel which is imported from access query. there are blank cell in table, when i apply function len() for the blank cell, i am getting 0. but when i am applying function 'isblank' for blankcell i am getting 'false'. even there is no space or data in cell it is showing isblank= false

When i click the cell once or click F2, cell becomes OK. i get result of isblank as true.

Any idea what is the issue and how to over come this.

Regards
Ajaar

 

[Hui]

Type the value 1 into a cell
Copy the cell
Select the Column
Paste Special, Paste Special, Multiply, Values

 

[ajaar]

Dear Hui,

Thank you for the reply.
I did exactly what you said, then blank cells are replace with '0'. but no changes in cells with issue ( with which resulf for isblank() as false, len() as 0)
when i click the cell once manually or click F2, it becomes OK.

Thanks
Ajaar

 

[Hui]

Can you please try this

Select the column or data area
CTRL+H to display Find/Replace dialog
Find: ALT+160 (Hold the ALT key and enter numbers via numeric keypad)
Replace: <leave blank>
Apply

 

[ajaar]

Dear Hui,
Thank you for the reply. unfortunately it didnt workout.
I am attaching sample here with. cell with issue is highlighted with yellow.

Regards
Ajaar.

 

[Khalid NGO]

Hi ajaar,
I think its the chars something like "" :
=IFERROR(formula,"")
which shows a cell blank but in real, cell is not blank..

you can try this:
select your range E3:F3
Press Ctrl+H (find and replace)

Find what: leave blank
Replace with: 1 (or any number to your choice)
Replace All
------------------
FIND & REPLACE (again)
Find what: 1
Replace with: leave blank
Replace All

Regards,