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) * 3)
Now you’ll need to concatenate both strings together using the concatenation operator, &.