Forum Discussion

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

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

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
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
  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
    Bronze Contributor
    Jul 25, 2018
    glad it somehow helped.. welcome