Adding digits to the right of a number so they are all 10 digits long

[Matthew Ingram]

hi there

i found this thread which nearly answered my question - but i can't get it to work sadly

i have a very long (thousands) string of sequential numbers like this (which is data over time)

3701041667

3702777777

3704513889

3705671296

370625

3706828704

3707638889

<colgroup><col width="306" style="width:230pt"> </colgroup><tbody>
</tbody>

I need to make all the numbers the full 10 digit string long - so the 370625 number into 3706250000
(and others like him that even shorter the full ten digits)

because it is a very long column (2389 lines long) i guess it is consideration that the formula works the entire length of the column

i would really appreciate help with this. i am a total excel newbie - an animator actually (wish my dad was still alive he was a hotshot!)

 

[Rick Rothstein]

thanks again Rick.

sorry to seem like an idiot - is there a shortcut to pasting the values or do i have to go cell by cell with the function (not shy of hard work but i may have to do this many times more...)

Simplest then would be to select Column B and then click the Insert button on the Ribbon's Home tab (Cells panel)... this will create a blank column next to Column A... put the formula into what is now the blank cell B1, then with cell B1 selected, double click the small black square in the bottom right corner of that cell... this will copy the formula down to just before the first blank cell in Column A. Now do your copy/PasteSpecial/Values and then delete Column B.

 

[Matthew Ingram]

Simplest then would be to select Column B and then click the Insert button on the Ribbon's Home tab (Cells panel)... this will create a blank column next to Column A... put the formula into what is now the blank cell B1, then with cell B1 selected, double click the small black square in the bottom right corner of that cell... this will copy the formula down to just before the first blank cell in Column A. Now do your copy/PasteSpecial/Values and then delete Column B.

that's absolutely magic! works perfectly. thanks very much Rick - much appreciated.

 

[Matthew Ingram]

sorry - one more thing!

is there a simple way to round this new figure (once it has had zeros added to its end) down to 5 or 6 digits?

perhaps in the same formula?

sorry to be a nuisance!

 

[taxcel rose]

Sorry, my fault. Please change all ; to , (I use an German locale)

 

[Matthew Ingram]

Sorry, my fault. Please change all ; to , (I use an German locale)

thanks taxcel rose. that works now.

i used this to round the number so all sorted now