Custom Number Format Tip: Add a Line Break in a Cell

• 2 mins read

In the image below the Custom  Number Format shown adds the word metres to the end of the number. As you can see in the Formula Bar, there is only a number in the cell. The number displays with the text.

Now let’s say you want to display the number on one line and the text on another line within the same cell. Unfortunately, the Wrap Text format doesn’t work as expected on a cell with a Custom Number Format like this.

Workaround

There is a workaround you can use to force a line break between the number and the text when Wrap Text is applied.

If you click between the number and the text in the Type: box in the Format Cells dialog and press Ctrl + J, it enters a line break character.

Then when you apply the Wrap Text format it will display the text on a different line within the cell. See the examples below.

Warning

The dialog is not built to display two lines. So, it only displays the first line of the Custom Number Format in the Type: box. You can see the full format listed at the bottom of the dialog.

This technique gives you the ability to control how numbers display whilst still allowing the numbers to be used in calculations.