Have you ever needed to know how many characters were in a cell?
It sounds a little ridiculous but I found myself in that exact position a couple of weeks ago.
I had an Excel template that we needed to use to upload some information that was originally created in a separate workbook. Unfortunately there were two issues. First, the template required a max of 255 characters in one of the fields. Second, the template didn’t tell you which rows were over the limit.
Enter the len formula. It’s often overlooked but it saved the day that day.
The way you use it is simple. Let’s say you receive an error message that says “There is a maximum of 255 characters for description.” All you do is go back to your Excel file and add another column. Type in =len(CellReference) and press enter. You fill down to all the rows and then filter on the ones above the cutoff.
You modify the ones that show up and watch the len formula result decrease as you rework your cell content. Once they’re all below your limit, go ahead and try to upload again or re-submit your file as needed.
Simple yet satisfying.
If the gif image above moves a little too quickly or you just want to have the video version sent to you, click the button below.
Leave a Reply