Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.

Examine the image below. You can see the different results in applying different alignment formats.

Note the default format for text is left aligned and for dates and numbers the default is right aligned.

The TEXT function converts numbers and dates into text and the output is then treated like text for formatting purposes. The TEXT function from row 17 is shown below.

The format codes used between the quotation marks are based on those codes used in the Custom Number Format section of the Format Cells dialog – see image below.

As an example of how this could look in a report see below.

The date in cell E1 is right aligned and it displays over cell D1, just like text. Note cell D1 needs to be empty for this to work.

The TEXT formula in cell E1 is shown below.

Using the TEXT function overcomes the date alignment issues you will face in Excel.