adding zeros to the end of numbers

[sosuzguy]

I have a list of numbers like this:
<TABLE style="WIDTH: 91pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=121 border=0><COLGROUP><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 91pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=121 height=20>1708388021</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>17080880000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.70832E+12</TD></TR></TBODY></TABLE>
I want to format them where they will all be in the same format:
17-083-88021-0000 (this one needed to have 4 zeros added at the end)
17-080-88000-0000 (this one needed only 3 zeros added)
17-083-20981-0000 (this one only needed 1 zero added)

And if I have one like 17083123451234 then it would automatically know that it had enough digits and to just add the dashes 17-083-12345-1234

Any idea how i can do this? Thanks a bunch.

 

[pagrender]

Hi,

Does this work for you?
=IF(LEN(A1)=14,LEFT(A1,2)&"-"&MID(A1,3,3)&"-"&MID(A1,6,5)&"-"&RIGHT(A1,4),LEFT(A1,2)&"-"&MID(A1,3,3)&"-"&MID(A1,6,5)&"-0000")

I can't tell if it will work for the 1.70832E+12 cell or not... but it looks like it works for the other cells.

 

[sosuzguy]

Thanks I'll try that but where do I put that format at ? I've never used one that long.

 

[pagrender]

Oh, sorry. I didn't realize you were asking to change the format only. I'm not exactly sure how to change the formats only. I'll play around with it to see if I can come up with something.

This is a formula you can put in an empty cell on your worksheet - it's similar to changing the format, but not exactly the same as you need extra real estate on your spreadsheet.

 

[West Man]

Perhaps: =TEXT(LEFT(A1&"0000",14)+0,"##-###-#####-####")

 

[sosuzguy]

Sorry I have no idea where to put these formats you guys are providing. I tried putting them into the "Type" field in the Custom format but that gives me an error.

What I'm trying to do is take a colum of data like this:
<TABLE style="WIDTH: 91pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=121 border=0><COLGROUP><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_2442007 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 91pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=121 height=20>1708388020</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1708388021</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>17080880000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.70832E+12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.7041E+13</TD></TR></TBODY></TABLE>

Highlight the whole column and then format the data to come out looking like 17-083-88020-0000

 

[Burrgogi]

Sorry I have no idea where to put these formats you guys are providing. I tried putting them into the "Type" field in the Custom format but that gives me an error.

sosuzguy,

What you need to do is enter those formulas into a blank column. For example, if your data is in column A, then in a blank column, copy & paste the formula that's been provided. If they are not located in column A, then you need to change the formula accordingly.

 

[sulakvea]

hi Sos, i've never needed to add zeros at the end (and quite frankly i am not sure why you would wanna do that ). unfortunately nothing comes to mind as a workaround. adding leading zeros is easy - just custom format the cell as 00-0000-0000-0000

however, here's a formula that would convert the number into a desired 14-digit integer (by adding however many zeros necessary). and after that you can custom-format your cells as 00-0000-0000-0000

=A1*10^(14-LEN(A1))

where A1 stores the original number. modify the reference to suit your needs.

HTH

 

[sosuzguy]

I work in the oil and gas industry. Every well has a unique number called an API number. It's composed of 17-083-12345-1234 The first set is the state, 2nd set the county, 3rd set the unique well ID number and the last set is an extension to tell you if it's a re-entry or side track, etc. Some of our systems do not account for the last 4 digits of the extension hence why when they are exported to a spreadsheet they come out with a few digits short. I don't need one that's 14 characters long, they should be 15 characters long. I tried coping and pasting the formula =A1*10^(14-LEN(A1)) in A1, a blank cell above the column of data. I guess I'm not familiar enough with these user defined formats because I can't get it to work.

 

[sosuzguy]

Sorry I wasn't thinking while ago. I got it thanks guys.