Excel text alignment with custom formatting

All we need is an easy explanation of the problem, so here it is.

Is there anything in a custom formatted formula which defines the text alignment?

I know the different parts of the formula is numbers, negative numbers and so on, but I need to set the alignment.

Example:

_*# ##0_;_*(# ##0)_;_*"-"??_;[email protected]_

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

While it is not possible to explicitly set the cell alignment using a custom format, you can achieve the same effect using the Repeat Characters syntax in the custom format. This syntax is simply the * character followed by the character that you want to repeat, in this case presumably the space character.

For example, the simple comma-separated number format “#,##0” can be made to align left by changing the format string to “#,##0* “.

Taking the example further, you could have number entries align left and text entries align right using the custom format string “#,##0* ;;;* @”.

Method 2

To directly answer the question regarding text alignment by a custom formatting string, it’s not possible. However, it’s possible to emulate 2 out of the 6 alignment options (left | center | right | top | middle | bottom) by using custom formatting settings to insert invisible characters.

To right-align text, as mentioned in another answer here, just leave the first three fields blank and use the Repeat Characters syntax * followed by a space, then an @ where you want the text to be inserted.

;;;* @

This will force the text to right-align, even if alignment type “Center” is applied to the cell.

There are some situations where you need to force text to left-align. For example, a Pivot Table displayed in Tabular Form or Outline Form can automatically merge and center cells with labels. What if you’d rather only merge, but want the text to be aligned to the left instead? The custom formatting rules can be used to post-process the formatting that was applied by the Pivot Table.

To left-align text, you have to be a little creative. In the Pivot Table situation, Excel will (annoyingly) trim trailing spaces in a custom rule before applying the format, so just using ;;;@*[space] won’t work. However, you can use one of the invisible Unicode characters instead, and Excel will leave your custom format unmolested.

The invisible Unicode characters are U0160 and U255. Either will work. On Windows, hold down Alt and type the numbers, for example Alt+2+5+5. The invisible character will be inserted after releasing Alt.

As far as the vertical alignment options, we’re stuck with either running a VBA script or using the alignment buttons in the ribbon. Custom formats can’t be used to apply, say, top alignment for example.


Example: forcing left/right alignment, even with cell alignment=center

Overriding center alignment in Excel table cells


Example: forcing left alignment in merged & centered Pivot Table label column

Overriding center alignment in Pivot Table

Method 3

Refer to https://www.auditexcel.co.za/blog/excel-refuses-to-align-a-number-left-or-centre/
It worked for me.
"As shown below, highlight the cells where the Excel numbers won’t left or centre align and:

go to Format Cells,
Click on Custom,
You will see the code that tells Excel to use the Accounting Format,
Delete the * in the format,
Click OK."

Method 4

It’s worth noting that, although it’s not possible specifically to set centre alignment using CF, it is possible to set cells as centre alignment and use the CF methods above to force left or right as required.

Method 5

Not exactly. I have to agree with @teylyn but you can do something that you might be able to adapt.

enter image description here

Method 6

The asterisk in the number format code represents the repeating character modifier. It is used along with a character to display a repeating digit that fills the empty space in a cell. If you think alignment cannot be set in an Excel custom numer format, then you should try a date format like “D __ MMMM * DDDD”, aligning day numer and name and name of month to the left, name of weekday to the right. Further columns may be required to add more formats, such as aligning double digit day number to the right.

Method 7

One more: if you want to force the text and numbers right-align, keeping the 1000 separator, use "#.##0;;;* @". If you want to keep "0", use "#.##0;;0;* @".

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply