Cell Not Empty but nothing in cell

[wjk221]

I know the title is confusing but I am having a problem I have never run into. I have data that has been imported from access and I am taking the information and pulling out what I need, I have this formula on one sheet {=IFNA(INDEX(Raw_Data2!$F$1:$F$10000,MATCH(J18&$D$1,Raw_Data2!$D$1:$D$10000&Raw_Data2!$E$1:$E$10000,0)),0)} and As you can see it is supposed to return a 0 if there is nothing in the cell. The problem I am having is that is instead giving me a blank cell. When I go to the data sheet the cell is blank and when I click into the cell there is nothing in the formula bar. If I do a clear contents then the formula returns a 0 like it is supposed to. I have another formula that is working off the value and there could be 1000 lines of data so to go through and clear all of the contents would be very time consuming. Any suggestions what is there or how to make it visible so I can write the code around it?

[ChemistB]

Try selecting the column and doing a Text to Columns> Finish and see if that fixes it.

Try =CODE(MID(A1,1,1)) and see if anything comes up.
where A1 is your "blank cell"

[LJMetzger]

You seem to have cells that have only spaces in them. The following macro may help.

Please Login or Register  to view this content.

Lewis

[wjk221]

ChemistB

Text to Columns worked perfectly, Thank You!!!!