insert zeros in a string of numbers

ggraves1968

New Member
Joined
Oct 2, 2008
Messages
43
I have a column of data that represents a frequency of occurance for an event.
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_653979 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>Frequency</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>345</TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD class=xl66 id=td_post_359243 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>12 7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>345</TD></TR></TBODY></TABLE>

I need the data to be 1 through 7, but if there is a missing interger it needs to be zero. So for example the first one should be 0034500 and the next should be 0000060.

I have the basic concept of trying to make something a certain lenght by using the below formual, but I need the zeros to be in a certain place depending on what is there.

=REPT("0",7-LEN(D2)) & D2

I could really use some help if anyone has a suggestion.

Thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try this formula

=TEXT(SUMPRODUCT(--ISNUMBER(FIND({1,2,3,4,5,6,7},D2)),{1000000,200000,30000,4000,500,60,7}),"0000000")
 
Upvote 0
I have airline flight schedules that come to me in a text file that I then bring into excel.

They have flights that occur for a given time frame on certain days of the week that are represented by the numbers 1 through 7.

So for the data set I have provided the filght operates on the 3rd, 4th and 5th day of the week from 3/15 to 4/3.

I need to get the data into columns 1 through 7 of my spreadsheet based on the position of the number. When I use the text to column feature it seperates the data, but i don't know how to make it parce it out to be in the right column.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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