Fitting long numbers into narrow cells

Excel - Formatting and Reports

Doesn’t it drive you crazy when a number doesn’t fit in a cell and making the cell wider messes up the beautiful layout you’ve created in another part of the worksheet? Arrgh!

Here’s a work around that will solve this problem. Turn the “too wide” number into text so it pushes through the side of the cell just like text does. When you want to refer to this “text number” in another formula you just turn the “text number” back into a number. Here’s how. If the too-wide number is in cell B12 and you need to display it in the narrow cell M15 use this formula in M15,

=TEXT(B12,”#,##0”)

The wide number will now show in M15. It squeeze out the sides of cells and you can align or indent it like text. Of course, you can use whatever custom numeric format you need in place of the “#,##0” in this example.

To later reference that “text-number” in cell M15 just reference it in a numeric formula and Excel will automatically convert it. If you want just the number by itself, then use,

=0+M15

That is zero plus M15.

Or use the VALUE function to turn the “text number” back into a calculable number, like this,

=VALUE(M15)

Share the power...