Function to find truncated entries?

  • Thread starter Thread starter EllenM
  • Start date Start date
E

EllenM

Hello,
I've exported an access table into excel. In the process, it looks like
some of the entries were truncated. How can I find all cells with 255
characters? I'd like to check each one for possible truncation. Is there a
function that will do this?

Thanks,
Ellen
 
You might try Conditional Formatting. Select the column that the entries are
in, then click Format/Conditional Formatting on Excel's menu bar. Select
"Formula Is" from the first drop down and copy/paste this formula into the
empty field next to it...

=LEN(A1)>=254

Change the column letter from my example "A" to the column letter(s) for the
column you selected above. Next, click the Format button, select the
Patterns tab and pick a color (a light tinted one works best), then OK your
way back to the worksheet. Any cells 254/255 characters long will be
highlighted in the color you selected. (I chose 254 just in case a trailing
space at the end of the entry might be removed by the process you are using
to import the text.)
 
Testing for text in A1 use =LEN(A1)
This will return the number of characters including white spaces.
Paul
 
Back
Top