How do I set a column of numbers to have same length (string) of character in Microsoft Excel when some of those numbers are negative and decimal?

Question

Let's say I have a list of the following numbers:

  97.24
  73.25
 -28.66
 -52.75
 -74.83
-234.43
-209.85
 176.64
-163.34
-398.64
  -3.73
 -75.16

How would I align them in a column such that they are all has 7 character length including the negative sign and its coma?

If original number has string length of 5, then I expect to add 2 zero behind last decimal number so that total string length = 7

For example, for above case it becomes,

97.2400
73.2500
-28.660
-52.750
-74.830
-234.43
-209.85
176.640
-163.34
-398.64
-3.7300
-75.160

I'm using windows and microsoft excel

Thanks in advance

Answer 1

Assuming you will always have a decimal place and just want to pad With trailing zeros you could use.

=A1&REPT(“0”, 7-LEN(A2))

This should append 0s to the length of 7. It will not account for adding decimal point if needed.

Answer 2

Select the cells you want to format then right-click on the selection and choose Format Cells.

Select the Custom option and set the type to 0000.00;-000.00 as below:

Positive numbers are given the format 0000.00 (what's before the ';') and negative numbers given -000.00 (what's after the ';').

These two formats use leading zeroes, not trailing, to ensure that decimal points are lined up. I knw that's not what you asked for I consider this way far more readable.

Answer 3

From the Format cell option you will find Custome tab the just select 0000.00;-000.00 and that it.

Answer 4

The steps to follow to set a column of numbers to have the same length of character in MS Excel are as below:

First, select the number of columns, and then make a right-click on it. Now, from the available options under the menu click on Format Cells, from here do the changes.