Excel will not allow you to create a formula that returns truly blank cell, causing the left-adjacent cell to truncate on display.
I figured out a workaround:
Format your adjacent cells (e.g. A1 and B1) with a fixed space font (e.g. Courier New) and have the null or error value returned by the formula in the right hand cell return this value:
MID(A1,INDEX(CELL("width",A1)-1,1),999)
You will have to make the left cell (A1) width a whole number of characters and may have to fiddle with the font size and integer value subtracted from the CELL function, but it does work reasonably well.
It simply displays the cells to the right of the string in A1 based on the column width in characters. The INDEX function is necessary because CELL width returns and array with two values; the cell width in characters and a true or false depending whether the column is the default width or not (we don't care about that true or false).parti
You can extrapolate this technique to additional right-hand cells that may contain formulae that you want to overflow the left-most cell (A1) if they return a null or blank. As before use a fixed space font and you may need to tweak the value subtracted from the INDEX(CELL) function.
This is particularly useful when using tables in which you want to used a formula in a column without having to individually delete it from individual cells on rows that may, say, be sub-headings.
Hope someone finds this useful.
I figured out a workaround:
Format your adjacent cells (e.g. A1 and B1) with a fixed space font (e.g. Courier New) and have the null or error value returned by the formula in the right hand cell return this value:
MID(A1,INDEX(CELL("width",A1)-1,1),999)
You will have to make the left cell (A1) width a whole number of characters and may have to fiddle with the font size and integer value subtracted from the CELL function, but it does work reasonably well.
It simply displays the cells to the right of the string in A1 based on the column width in characters. The INDEX function is necessary because CELL width returns and array with two values; the cell width in characters and a true or false depending whether the column is the default width or not (we don't care about that true or false).parti
You can extrapolate this technique to additional right-hand cells that may contain formulae that you want to overflow the left-most cell (A1) if they return a null or blank. As before use a fixed space font and you may need to tweak the value subtracted from the INDEX(CELL) function.
This is particularly useful when using tables in which you want to used a formula in a column without having to individually delete it from individual cells on rows that may, say, be sub-headings.
Hope someone finds this useful.
