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, &.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
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.
very neat method 🙂 thanks for sharing …. and what font did you use for the red text (events) looks nice
I think it's called Segoe UI Print