Identifying cells by text length

By Neil Shorney, Director, Naturally Sales Ltd.



I received this email from a recent student on our live e-learning recently, and thought it would make a useful topic for an e-tip:

“I work a lot with mailing lists, and problems arise when the company and address fields contain more than 40 characters. Is there a way to indicate these fields when they contain too many characters in such a way that I can filter on this information? Right now I am simply eyeballing the list and making changes but I may receive lists with 10,000 entries!”

I suspect this might not be a unique problem, and there are two ways we can look at this problem, depending on where the data comes from. If the data already exists in the spreadsheet, then we can use a formula to indicate those cells which match certain criteria in order to sort by these cells; in this case those which contain more than 40 characters.

I'd recommend typing the formula into a new column, by which you can then filter to highlight those records you're identifying.  To highlight cells which contain more than a certain number of characters, you can use the LEN function (short for ""length""). It works like this:

      =LEN(cell_ref)

You can combine this with an IF statement to highlight certain conditions. For example, if you want to know whether C14 has more than 40 characters, you could type the following formula into, for example, D14:

      =IF(LEN(C14)>40,""Too long"","""")

...which would give a blank cell if less than 40, or say ""Too long"" if greater than 40.

Why not try this with other criteria?

The next step is to remove those characters after the fortieth in a cell. That's a topic for next time, then in the following e-tip we'll look at how to prevent users being able to enter more than 40 characters in the first place!