4

I'm trying to add trailing zeroes to the right of a decimal number in Excel. I have cells with these values:

6.13
31.41592
2.124

How can I make them all have 6 decimal positions like this?

6.130000
31.415920
2.124000

EDIT: After adding the trailing zeros, I need to use the resulting values inside another formula. That is why the Format Cells dialog won't work.

Thanks!

4 Answers 4

11

If you are trying to concatenate the value with something else (based on your comment above), you can use the TEXT function to specify a number format for the decimal in your formula. For example, if you have a decimal 3.25 in A1 and grams in B1, you can specify 6 decimal places with the following:

=TEXT(A1,"#.000000")&" "&B1

This should result in 3.250000 grams.

1
  • Thanks, I was about to use a more complicated formula calculating the number of places after the point: =A1&REPT("0",6-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1)))). This one looks far better! Aug 5, 2014 at 14:33
5
  1. Get to the Format Cells dialog (e.g. by right-clicking a cell and selecting "Format Cells...")
  2. On the Number tab, select Number from the list of Categories.
  3. Set the Decimal Places box to 6.
  4. Hit Ok.
2
  • I have tried this, however I need to use the result to concatenate it with some text. When I use it on the new formula the trailing zeros disappear. Thanks. Aug 5, 2014 at 14:20
  • 1
    I recommend updating your question with that information even though you already have your problem solved, in case others have the same problem.
    – Steve
    Aug 5, 2014 at 15:12
1

Highlight the cells you would like to have 6 decimal points, then go to Format > Cells... and from there select the number category, from there you can choose how many decimal points you would like.

-1

You can delimit your number by the decimal point, add trailing zeros, and finally combine your whole number with your decimal number.

Example, turn 6.13 into 6.130000

In cell A1 type 6.13

Use the text to columns tool (under the data ribbon) and delimit by "."

A1=6

B1=13

In cell C1 type the formula =B1&(REPT("0",6-LEN(B1))

C1=130000

Finally in cell D1 type the formula =A1&"."&C1

D1=6.130000

Hope this helps.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .