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.

Assumptions

  1. The file is in a format that can be parsed by Excel (tab delimited, CSV, etc…)
  2. 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

  1. Open the file in excel (parse it if it’s text)
  2. Insert a new row above the header row
  3. 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

  1. Select both the row with the formula in it and the header row for all columns with values and press ctrl+c (copy)
  2. In a new sheet under the Home > Paste > Paste Special
  3. 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

Hope this helps!

bsullinsExcel Tip – Identifying the max length of values in a column
  • http://dvdplayersrecorders.net/ Warner Mondo

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

  • http://www.kingjoker313.com/index.php?do=/adidasbounce1/blog/adidas-f50-adizero-trx-fg-piping Kelsey Rodgers

    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.

  • http://www.jhurstsplayground.com/index.php/member/20228 ears buzzing

    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.

  • dany.benjamin

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