+ Reply to Thread
Results 1 to 3 of 3

Preventing cell overflow into next cell (Solution)

  1. #1
    Registered User
    Join Date
    08-28-2008
    Location
    US
    Posts
    1

    Preventing cell overflow into next cell (Solution)

    I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this.

    The question:
    How can you prevent a cell's contents from overflowing into the next cell?

    Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.

    Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.

    The solution:
    Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
    Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.

    Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.

    Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.

    Tested in Excel 2002

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561
    FWIW;
    I would strongly discourage doing this
    bigtheta's already alluded to the potential problems this can cause so I hope won't mind me slating it...

    If you're designing something to look pretty - fine - ask yourself if the specific design you have right now is worth preserving if it can cause potentially misleading outputs to the intended users (pretty things are meant for others, no?). I know I'd rather have something that worked the way I'd expect!

    Or, at the very least, try using a safety method like this:
    =LEFT("big long expression",10)&IF(LEN("big long expression")>10,"…","")

  3. #3
    Registered User
    Join Date
    02-23-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Preventing cell overflow into next cell (Solution)

    I like the idea of having a formula. How would you create a formula that would show the beginning of the text and not the rest?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1