Excel: How to find cells exceeding a character limit

The example I’m using to demonstrate relates to product IDs and descriptions in WordPress, but many stock management systems have character limits you need to be aware of when importing data.

For example our system has a 30 character limit and special characters like or & will expand in HTML to take up at least 4 characters. All IDs and Descriptions are only examples.

  • Select the column/s you want to work with. In this case, column A + B.
  • Click on Conditional formatting and then click on New rule.
  • Select a Rule Type: Use a formula to determine which cells to format. In the box for Format values where this formula is true, enter the following:
    =LEN(A1)>30
    (you can replace 30 with a number of your choice to check for a different number of characters)
  • Click the Format… button to set how you will mark the cells you are looking for. I’m highlighting mine by clicking the Fill tab and then selecting a color. Click OK to go back to the New Formatting Rule dialog box.
  • It will show you a preview of what selected cells will look like. Click ok to find the cells.
  • The cells with more than 30 characters are now highlighted.

Most Recent:

Random Pick:

MYOB crashes sending emails

We’ve customers experiencing an issue with MYOB and Office 2016 where it crashes when sending emails. It is a known issue that MYOB are investigating

Read More »