How to create an Excel formula that allows the left-adjacent cell to overflow if the value is blank or error

Mrock1

Board Regular
Joined
Oct 7, 2014
Messages
77
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hello Mrock1

While I don't need this at the moment, I have bookmarked it for possible future use. When I get a chance, I'll set up a situation as described and try your solution as I can see where it may come in handy. Thanks for posting this as I'm sure other people may find a use for it.

TotallyConfused
 
Upvote 0
Could you please upload a sheet so I can study the formula MID(A1,INDEX(CELL("width",A1)-1,1),999)
It may be something i need
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top