Padding with "0"'s

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129
At one time, i knew how to do this. It's been a LONG week.

I have a column with sequentially numbered rows, starting with "1". What i need to do is "pad" each row with a prefix of "0"'s to make an equal string length for each row of 7 characters. Column B, starting with Row 4 is where "1" begins. column AE, starting with Row 4 will be where the new data resides. In the case of B4, the new value would be 0000001, in the case of column B13, the new value in AE13 would be 0000010. What's the formula i throw into column AE to make this happen??

Thanks in advance

james
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

FKoenig

Board Regular
Joined
Jul 27, 2011
Messages
85
At one time, i knew how to do this. It's been a LONG week.

I have a column with sequentially numbered rows, starting with "1". What i need to do is "pad" each row with a prefix of "0"'s to make an equal string length for each row of 7 characters. Column B, starting with Row 4 is where "1" begins. column AE, starting with Row 4 will be where the new data resides. In the case of B4, the new value would be 0000001, in the case of column B13, the new value in AE13 would be 0000010. What's the formula i throw into column AE to make this happen??

Thanks in advance

james

I only know a "cheat" way to do it.

Select the entire column and "format cells"

Select custom and enter the below into the "Type:" box
Code:
0000000

Copy the data out of the column and paste into Notepad

Reformat the column again to txt

Copy the data back out of notepad and paste it over the data in excel again.
 

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129
Interesting. I've never seen it done this way before. With Excel 2010, there was no need to copy and paste anything. As soon as i set the "type" to "0000000", it padded each row exactly as i wanted.
Go figure. MS made something "simpler". I'm either suffering from head trauma or am in the bizarro universe now.
 

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129

ADVERTISEMENT

ack. Bad news. When i go to use that column, with it's changes in a concatenate statement in another column, it removes those zeros and puts it back to it's original state. Not what i needed:confused:
 

FKoenig

Board Regular
Joined
Jul 27, 2011
Messages
85
Interesting. I've never seen it done this way before. With Excel 2010, there was no need to copy and paste anything. As soon as i set the "type" to "0000000", it padded each row exactly as i wanted.
Go figure. MS made something "simpler". I'm either suffering from head trauma or am in the bizarro universe now.

It will change the display to "0000001", but it will not change the actual value of the cell. If you need to change the actual values of the cells, you'll need to do the copy/paste bit.
 

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129

ADVERTISEMENT

Wally,

Thanks. That's what i was looking for.
 

WJHamel

Board Regular
Joined
Oct 7, 2011
Messages
129
Sorry, but there's still something wrong here. My destination column (AE4) is where i applied the formula, using: =REPEAT("0",7-LEN(B4))&B4
because B4 is where my preset sequential numbers start. I've formatted both columns as number type and i get the flippin "#NAME?" error in AE4. Same happens if i set the columnn type to General or Text. What am i missing?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,078
Members
414,500
Latest member
kevdragon1

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
Top