Length in a cell

Question

I have a large Excel spreadsheet with one column containing a 3-character client ID. Sometimes these client IDs are entered (wrongly) as 4 and 5 character IDs.

Is there a simple test I can use to check for (? list) those entries where the ID is longer than 3 characters?

lorenzo kim · Answer

Mr. Stewartyou can use the DATA &gt; Data Validation to control the number of characters in a column or cell..pls see below image&nbsp;

ross stewart · Answer

Thanks Lorenzo, but if I opt to circle the errors, I can't see how to sort these to the top of the table, so that they can be fixed.

I was hoping for some way of running a little report which might extract, or display together, the invalid entries. (Currently the table is around 1200 entries but will be growing each month).

Any thoughts?

lorenzo kim · Answer

Mr. Stewart I am attaching herewith a test.xlsm for you to try - copy your data here and test it. (do not test directly on your workbook - always make a backup copy of your work) - press DEVELOPER &gt; Macro (or press ALT F8) a dialog box will appear - press Run For the SUB &gt; you can change "A" - to the column you want - also I am assuming you have a Header and data starts at row 2.. It will add number one to the cell/s not equal to length 3. You then sort that column (do not forget to mark all columns) - the cells with "1" will be on top - you then can make your corrections. Finally - copy the corrected sheets back to the original workbook. (while maintaining a backup copy of the original - just in case).. I am recommending that you do the DATA &gt; Data Validation to prevent future errors. Hope this helps

lorenzo kim · Answer

Mr. StewartI am attaching herewith a test.xlsm for you to try -copy your data here and test it.(do not test directly on your workbook - always make a backup copy of your work) -press DEVELOPER &gt; Macro (or press ALT F8)a dialog box will appear - press RunFor the SUB &gt; you can change "A" - to the column you want -also I am assuming you have a Header and data starts at row 2..It will add number one to the cell/s not equal to length 3.You then sort that column (do not forget to mark all columns) -the cells with "1" will be on top - you then can make your corrections.Finally - copy the corrected sheets back to the original workbook. (while maintaining a backup copy of the original - just in case)..I am recommending that you do the DATA &gt; Data Validation to prevent future errors.Hope this helps

lorenzo kim · Answer

Mr. Stewart maybe a "simpler" way.... add a column besides your ID column - in that added column place the formula =len(A1) where A1 is the column of ID, then copy the formula down.. sort the sheet in accordance with that added column -- you will now see the variations..after that you can delete the added column.. HTH

Forum Discussion

Length in a cell

8 Replies

Resources