Excel Tip: Identifying the max length of values in a column

Sometimes files are received such as client feeds that contain values in columns that exceed the maximum number of characters allowed in the destination database. When these records are imported an error occurs when trying to fit this value into the column with the character limitation.

In these cases it is helpful to have a quick way to identify which fields and specific records are causing the issue. Ideally the ETL process that loads the data would provide this information to us but, in the spirit of catching errors before they occur, here is a quick way to accomplish this in Excel.

Contents

Assumptions

The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
The number of rows does not exceed the maximum allowed by the version of MS Excel being used.
- Excel 2003 = 65,536 rows
- Excel 2007+ = 1,048,576 rows

Steps

Open the file in excel (parse it if it's text)
Insert a new row above the header row
Enter the following formula in every column of the new row that is being imported
- =MAX(LEN(A2:A65636))
- The range inside the forumla should cover all of the rows in the column (eg. A2:A100) for records 2 – 100
- In order for this formula to work properly you must press ctrl+shft+enter after typing in the formula.
- The best way to get this into all the additional columns would be to simply copy and paste.

At this point you will be able to see the max length of the values in the columns with the formula entered. To make this more readable and even easier to identify try completing these additional steps below.

Additional Steps

Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
In a new sheet under the Home > Paste > Paste Special
From the paste special dialogue box check ‘Values' and ‘Transpose' then click ok

You will now see the fields and their max lengths stored vertically instead of horizontally as they were on the original sheet which I've found to be the easiest to read.

Want to learn more about Excel?

Take my course on Excel Dashboarding

Or dive in deeper and learn Data Analysis Fundamentals on Pluralsight.com

Hope this helps!

10 Comments

there’s a bug with the website using OPera the text is weird :/

What would we all do without the wonderful concepts you pick out on this site? Who has obtained the tolerance to manage critical topics within the interest of typical subscribers like us? My spouse as well as i and my pals have become happy to possess your blog among the types we commonly visit. It is hoped you recognize how very significantly we enjoy your effort! Best wishes through all of us.

Wonderful goods of your stuff, man. I’ve got understand your stuff before and you’re just too fantastic. I actually like what you’ve acquired here, really like what you are stating and the way in places you say it. You create it enjoyable and you still manage to help keep it sensible. I can’t wait you just read a lot more from you finding out. This is really a terrific site.

what does ctrl+shift+enter do in excel? I noticed it only works when I do this. Just curious what it does.

Ctrl+Shift+Enter enters the range as a matrix, being calculated one by one. Thus, this formula calculates the lenght of each cell. Otherwise Excel would try to calculate the length of a range, which cannot be computed and produces the #VALUE error since length function admits only one parameter.