Everyone! We've moved to WordPress.

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:


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.


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:

=LEFT("…",LEN(B3)>25)

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, &.


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:
...mmmmmmuch better!

Download my example: Spillover Design Example.xlsx

Comments: 5

  1. 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
  2. very neat method :) thanks for sharing .... and what font did you use for the red text (events) looks nice

    ReplyDelete