March 21

Truncating Long Text to Fit into Cells


Have you had a list of names but found your cell width too small to show every name?  Take a look at this:

7e07e spilloverdesignitenerary

I had my nonexistent secretary print out this fake itinerary for me.  Nothing lines up neatly.  Look how some of my events overlap the cell boundary between columns B and C.  My 2012 End-of-world Steering Committee meeting on February 1st, 2013 goes long and is then cut-off by the date!

To fix this, you could have Excel automatically size Column B, but that’s a bit unpredictable to have on a dashboard (what if other content is sized off the screen?).  We’ll just have to accept that we can’t show everything, but we can make it look neater.  We’ll employ some type of text truncation method similar to what Microsoft uses to display the names of files in Windows Explorer: if the text to be displayed is greater than a certain character length, simply truncate and add a “…”.

The “Original Recipe” Method

First thing we’ll need to do is figure out how many characters we’ll want to display before truncation.  There’s really no way to do this without trial and error. But, to make life easier, we can use =LEN(“Your Text Here”) to return the length of your text; or, we can use something like  =LEFT(B3, 4) to return the first four characters of text in cell B3.  Keep trying until you find a good character length.  

Hopefully, you got a good handle on using LEN and LEFT above because we’ll be using them from here on out.  Also, I’m going to use 25 characters as my desired character length before truncation.  Below I use  LEFT to pull the first 25 characters from each event name.

6a192 spilloverwleft

Now we’ll need to know which names to add the “…” to.  Following ExcelHero’s discussion on using IFs, we’ll avoid using one here; not to worry, we don’t really need it.

As it turns out, LEFT can take a zero value character length — it just returns nothing.  So to have Excel automatically return what we want, we’ll do this:


Take a look at that second argument on the right.  It takes the length of our event name and tests if it’s greater than our cutoff length of 25.  If it’s less than 25, Excel will return a zero and display nothing.  If it’s greater than the cutoff, Excel will return a 1 and display the first character in our text.  But there’s something I found out while writing this tutorial, I expected Excel to only return one period (“.”), but instead it returns all three (“…”) even if you only put a 1 in on the right.  I’m guessing that the ellipses are treated as one character; however, I’m dubious that this works in every case on every computer, so to have your formula return three characters or zero, you’ll simply change it to:

=LEFT(“…”,(LEN(B3)>25) * 3)

Now you’ll need to concatenate both strings together using the concatenation operator, &.

dc42f concatenatespillover
Finally, as you can see in cell, H7, there is a space between the event name and the ellipses.  This is because the last character before the cutoff was a space.  To fix this, surround the first string formula with the TRIM function; this will automatically delete any leading or ending spaces.  
Putting it all Together

To finish, we’ll combine all our steps into one formula, like so:

=TRIM(LEFT(B3,25)) & LEFT(“…”,LEN(B3)>25)

To get:
bee76 spilloverfinal
…mmmmmmuch better!

Download my example: Spillover Design Example.xlsx



You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
  • That's a pretty neat trick. I didn't know you could use logic operations in the LEFT function. But I've never been too bothered with autofitting the width of the cell or resizing it manually as needed. I'll do it for fun at least once — because that's my idea of a good time!If anything, this is a good lesson in succinctness by avoiding the IF function.

  • I hear ya. I devised this solution to solve a problem I frequently see on Excel dashboards, but not really anywhere else. I chose the itinerary for this example because I figured it would be more approachable. But I'm glad you still found it useful. In fact, the real goal of this blog is to stress the underlying mechanics of Excel (and how these form "reusable components" in other spreadsheets and applications). So, if I'm doing a good job, the development process should be more important than the end goal. Hopefully, I've achieved that.Anyway, thanks for following and thanks for posting.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.