Forum Discussion

Ross Stewart's avatar
Ross Stewart
Copper Contributor
Jul 24, 2018

Length in a cell

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 c...
excel
Formulas and Functions
Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 24, 2018

Mr. Stewart

you can use the DATA > Data Validation to control the number of characters in a column or cell..

pls see below image

 

Ross Stewart's avatar
Ross Stewart
Copper Contributor
Jul 24, 2018

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's avatar
    Lorenzo Kim
    Bronze Contributor
    Jul 25, 2018
    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
    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Jul 25, 2018
      maybe the right formula if you have a Header would be
      =len(A2) where A is your ID column
      • Ross Stewart's avatar
        Ross Stewart
        Copper Contributor
        Jul 25, 2018

        Thanks Lorenzo,

        Works a charm.

        I used  =IF(LEN(F2)=3," ","XXX")  and then sorted the resulting column (G in this case) so that the XXX were at the top;  we could then identify the errors and have them corrected. 

        (The originating software is not ours, so we needed an ambulance at the bottom of the cliff rather than a fence at the top which would have been more logical and better!)

        Many thanks from New Zealand

        Ross

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Jul 25, 2018
    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 > Macro (or press ALT F8) a dialog box will appear - press Run For the SUB > 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 > Data Validation to prevent future errors. Hope this helps
    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Jul 25, 2018
      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 > Macro (or press ALT F8)
      a dialog box will appear - press Run

      For the SUB > 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 > Data Validation to prevent future errors.
      Hope this helps

Resources