How do you find and delete invisible formatting/code in Excel 2007?

[amr4434]

I have three columns of data (I have renamed them for simplicity):

Category A
A1 = Trial 1
B1 = Trial 2
C1 = 'Best of' Trials 1 & 2

I used the 'If' formula to calculate the 'best of' column.
(ex. Statement A1>B1, if true C1=A1; if false C1=B1)

There are approximately 6,000 rows of data. I copied and pasted the 'IF' formula to all rows in C1. I just realized that a large majority of the cells copied correctly, however some are blank.

The data was imported from .txt files which were exported out of another program. I fear that there is some sort of invisible formatting or coding which may be preventing the formula from copying correctly. Is there a way to find and delete it?

I have tried 'clear all', 'clear formats' and 'delete' in the blank cells. It did not fix the problem.

The data I am analyzing is protected by FERPA. The actual data is more complicated and there are about 7 different categories (each with 3 respective columns as seen above) that are experiencing the same issue. Thanks in advance for suggestions.

 

[amr4434]

I left out one important part of my previous post. There are some cases where B1 is intentionally blank (where trial 2 did not occur). It is some of those blank cells that are causing the problem.

I think I may have figured this out (4 hours later-arrg!). After much investigating, I discovered that the offending cells have spaces. Mind you - they LOOK blank. The number of spaces vary depending on the expected value (3 digits = 3 spaces). Ironically some of the blank cells are perfectly fine (no spaces). In those instances the 'If" formula worked.

I have used conditional formatting to locate the spaces. Not sure how to remedy the issue large scale, but at least I can SEE where the problems are.

I still welcome feedback and suggestions. Just thought I would update in case anyone else has a similar problem.