How to reposition the 0 at the back of the number that is being inputted in the cell using the forma-CodePudding

I'm curious to ask if there is a way to format the cell in excel to have the remaining number be 0 at the back.

For example: i wanna input the 299256889 but i need it to be formatted and become: 299-256-889-000.

When formatted the cell and input the type: 000-000-000-000, it works but the problem is it formatted into this: 000-299-256-889, the 0 was positioned at the opposite side of i want it to be.

CodePudding user response：

You can achieve this using the Ampersand operator. Select the cell to which it applies and in the function part (input field above the cells prepended by 'f') set the value to the row, with the desired text like following:

Provided that you selected the B column and first row

=B1&"-000"

Hope this helps solving your problem. More solutions for adding text are provided here:

Note that this is really only formatting the first nine digits, and adding a string to the end. The value stored in the cell will be the nine digit number, and the value displayed will be as you want. If that is not satisfactory, please be more specific

CodePudding user response：

Alternatively you could use the following:

###-###-###-###, %%%

The space after the comma in the custom format is <ctrl j> which will make sure the %%% are on the other line.

This results in:

It actually results in 2 lines; the top line being the number and the bottom line being the %%%. So make sure you have both top align and wrap text enabled:

This version also allows numbers with digits to be adopted in the same format (see second number in the example).

More info on this can be found here