Padding with "0"'s

[WJHamel]

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

 

[FKoenig]

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

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]

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.

 

[WallyBackman]

In B4 put =REPEAT("0",7-LEN(A4))&A4 and then copy it down beside all Col A entries.

 

[WJHamel]

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

 

[FKoenig]

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]

Wally,

Thanks. That's what i was looking for.

 

[WJHamel]

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?

 

[WallyBackman]

WJ,

Sorry. That's =REPT( instead of =REPEAT(

 

[WJHamel]

Thanks. That looks more familiar.