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

M

Matthew Ingram

New Member
Joined
Jun 2, 2017
Messages
9
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!)
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Matthew Ingram said:
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

<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!)
Click to expand...

Considering that your Number is located in A1, put this formula on A2

=TEXT(A1,"0000000000")
 
Upvote 0
Hi,

if you don't want to calculate with the result:
=A1&REPT("0";10-LEN(A1))

if you need a number as a result:
=--(A1&REPT("0";10-LEN(A1)))

if there are numbers with more than 10 digits:
=--(A1&IF(LEN(A1)>10;"";REPT("0";10-LEN(A1))))
 
Upvote 0
thanks for this

however that adds digits to the left - not the right

so for 370625 it gives me 0000370625 not 3706250000

you see 37 corresponds to minutes 06 to seconds and 625 to milliseconds
 
Upvote 0
Jerome_RA said:
Considering that your Number is located in A1, put this formula on A2

=TEXT(A1,"0000000000")
Click to expand...

thanks for this

however that adds digits to the left - not the right

so for 370625 it gives me 0000370625 not 3706250000

you see 37 corresponds to minutes 06 to seconds and 625 to milliseconds
 
Upvote 0
taxcel rose said:
Hi,

if you don't want to calculate with the result:
=A1&REPT("0";10-LEN(A1))

if you need a number as a result:
=--(A1&REPT("0";10-LEN(A1)))

if there are numbers with more than 10 digits:
=--(A1&IF(LEN(A1)>10;"";REPT("0";10-LEN(A1))))
Click to expand...

thank you very much for posting this. i get an error message with this saying "there is a problem with this formula"

thank you for your time
 
Upvote 0
Jerome_RA said:
Considering that your Number is located in A1, put this formula on A2

=TEXT(A1,"0000000000")
Click to expand...
That puts the zeroes on the wrong side of the number



taxcel rose said:
if you don't want to calculate with the result:
=A1&REPT("0";10-LEN(A1))
Click to expand...
Another way...

=LEFT(A1&"0000000000",10)



taxcel rose said:
if there are numbers with more than 10 digits:
=--(A1&IF(LEN(A1)>10;"";REPT("0";10-LEN(A1))))
Click to expand...
Another way...

=--LEFT(A1&"0000000000",10)



taxcel rose said:
if there are numbers with more than 10 digits:
=--(A1&IF(LEN(A1)>10;"";REPT("0";10-LEN(A1))))
Click to expand...
Another way...

=--(A1&REPT("0",MAX(0,10-LEN(A1))))



Matthew Ingram said:
thank you very much for posting this. i get an error message with this saying "there is a problem with this formula"
Click to expand...
taxcel rose's Excel version uses a semi-colon delimiter instead of a comma... if you change those, his formulas will work for you. Or you can use the versions I posted above.
 
Last edited:
Upvote 0
Rick Rothstein said:
That puts the zeroes on the wrong side of the number

Another way...

=LEFT(A1&"0000000000",10)
)
Click to expand...

hi Rick

that's great. that works well.

HOWEVER is there a way it can apply to the whole column so i don't have to edit the string for every line? otherwise i have to change it to A2, A3, A4, etc right up to line 2390

really appreciate your help with this.

thank you very much
 
Upvote 0
Matthew Ingram said:
hi Rick

that's great. that works well.

HOWEVER is there a way it can apply to the whole column so i don't have to edit the string for every line? otherwise i have to change it to A2, A3, A4, etc right up to line 2390

really appreciate your help with this.
Click to expand...
If you will only be doing this once, you can place the formula on Row 1 of an unused column, copy it down to Row 2390, then copy the column and then PasteSpecial Values over top of your existing values in Column A (then delete the column with the formulas). Otherwise, you would need a VBA macro... is that what you want?
 
Upvote 0
Rick Rothstein said:
If you will only be doing this once, you can place the formula on Row 1 of an unused column, copy it down to Row 2390, then copy the column and then PasteSpecial Values over top of your existing values in Column A (then delete the column with the formulas). Otherwise, you would need a VBA macro... is that what you want?
Click to expand...

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...)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
List of all Combinations of Row with 10's of Millions of Combinations
Replies
2
Views
930
strat919
S
V
  • Question
Extract only the numbers, if possible the Lat & Long
Replies
4
Views
577
Peter_SSs
Peter_SSs
K
  • Question
Adding a dash after the first five digits (for a ten digit number), and after the first five and ten digits (for a twelve digit number)
Replies
7
Views
1K
mikerickson
mikerickson
B
  • Question
Search a column of cell for a 4 digit number that always starts with the same two digits
Replies
2
Views
413
BusinessPlanner
B
M
  • Solved
Add Zeros to make all digits in to 7 numbers
Replies
3
Views
353
Jonmo1
Jonmo1

Forum statistics

Threads
1,214,397
Messages
6,119,273
Members
448,883
Latest member
fyfe54

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top