Hi, I have a spreadsheet which has data imported from a very old excel format (excel 2.0). There are cells in this file which appear blank however excel 2017 does not perceive them as such.
The really odd thing here is that if i use F2 to edit the cell and then hit enter or Tab to move off of it, the cell is suddenly recognized as blank. I also get a similar result using Find/Replace and Special Cells Blank. If i use CTRL+G and then try Blank cells none are found, however if i use Find/Replace and do not enter anything into the Find box it recognizes all of the cells as empty and i can replace them without a problem. I tried to use CLEAN however after pasting the values of the clean it is still not an actual blank cell. One other test i just tried as i was typing this is =B2="" and it returned TRUE, which is even more confusing to me since I would think that this would indicate that the cell is actually blank.
I copied a row 6 of the text into Notepat++ and then pasted it into this utility to find out what the actual characters were https://software.hixie.ch/utilities/cgi/unicode-decoder/character-identifier and the result i got is:
Character number 4 is decimal 9, hex 0x09, octal \011, binary 00001001
U+0009 <control>
= CHARACTER TABULATION
= horizontal tabulation (HT), tab
Ultimately i need to be able to recognize these cells in a formula which is checking for blank cells. Since this is being done in VBA i am also fine stripping the data from these cells if i could just figure out what the contents actually are. Any help is greatly appreciated here.
it does not look like i have permission to attach the file here so i posted it here https://drive.google.com/open?id=1VC3EGypXemra_uKd5JsaKm02ICio2j61 however here are the tests that i ran in the file itself (on the last test the formulas are below rather than next to)
<tbody>
</tbody>
</control>
The really odd thing here is that if i use F2 to edit the cell and then hit enter or Tab to move off of it, the cell is suddenly recognized as blank. I also get a similar result using Find/Replace and Special Cells Blank. If i use CTRL+G and then try Blank cells none are found, however if i use Find/Replace and do not enter anything into the Find box it recognizes all of the cells as empty and i can replace them without a problem. I tried to use CLEAN however after pasting the values of the clean it is still not an actual blank cell. One other test i just tried as i was typing this is =B2="" and it returned TRUE, which is even more confusing to me since I would think that this would indicate that the cell is actually blank.
I copied a row 6 of the text into Notepat++ and then pasted it into this utility to find out what the actual characters were https://software.hixie.ch/utilities/cgi/unicode-decoder/character-identifier and the result i got is:
Character number 4 is decimal 9, hex 0x09, octal \011, binary 00001001
U+0009 <control>
= CHARACTER TABULATION
= horizontal tabulation (HT), tab
Ultimately i need to be able to recognize these cells in a formula which is checking for blank cells. Since this is being done in VBA i am also fine stripping the data from these cells if i could just figure out what the contents actually are. Any help is greatly appreciated here.
it does not look like i have permission to attach the file here so i posted it here https://drive.google.com/open?id=1VC3EGypXemra_uKd5JsaKm02ICio2j61 however here are the tests that i ran in the file itself (on the last test the formulas are below rather than next to)
| ID | Blank Cell | Test | Formula for Test | |
| ME-Q | FALSE | ISBLANK(B2) | ||
| ME-Q | TRUE | B3="" | ||
| ME-Q | #VALUE! | CODE(B4) | ||
| ME-Q | TRUE | CLEAN(B5)=B5 | ||
| OE | TRUE | istext(B6) | ||
| ME-Q | TRUE | ISBLANK(B7) after double cliking the cell | ||
| ME-Q | x | 120 | CODE(B8) after using Ctrl+h and putting nothing for search value and x for replace value | |
| ME-Q | FALSE | |||
| Clean(B9) | Paste Values from C9 | isblank(D9) |
<tbody>
</tbody>
</control>
