add leading zeros in a column?

[cjbrown815]

Hello,
When data is populated into excel 2007 by one of our instruments, it is truncating the leading zeros and we have to manually put them back in. The number of characters are not the same. Is there an easy way to add a zero to the left of all data in a column?

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Hi,

Leading zeros in numbers is meaningless. 1 = 01 = 001, and Excel does not STORE leading zeros fro numbers.

Leading zeros in numeric STRINGS, that are not really numbers but IDENTIFIERS are significant.

So what are you referring to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

-> When data is populated into excel 2007 by one of our instruments

How is Excel being populated? You might be able to force that column to TEXT instead of NUMBERS.

If you just need it to look right, you can just change the format: Data > Format > Cells > number > Custom, then change it to something like 00000.

NOTE: [!]this does NOT change the data itself!!![/!] It only makes it appear to be the right number of characters.

If you really want to add in the zeros (which, depending on what you're doing with it, is likely a good idea), then you can concatenate a zero and the contents of the cell:
[tab][COLOR=blue white]="0" & A1[/color]

-> The number of characters are not the same

Do you mean you want to insert a varying number of zeros? To always bring the total number of characters up to a certain number? If so, then try this:
[tab][COLOR=blue white]=Rept("0", 10 - Len(A1)) & A1[/color]
(Change the "10" to the number of characters you want)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

Little slo on the uptake. Draggin' on Fri PM? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

Hi,
I need to add a leading "0" for the next application this data gets sent to. The spreadsheet gets populated without the "0"'s and I need to add them before I send them to the other app. "0" + cell seems to be the right way. Can I make this happen for the whole column?

Basically the data looks like this

1245
133333
12
200524256
23233

I need to add a leading 0 for the whole column, preferably automatically. thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

Format leading zeros.

Set FONT for ALL CELLS to Courier New

SaveAs .PRN text file

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cjbrown815]

->Format leading zeros.

Set FONT for ALL CELLS to Courier New

SaveAs .PRN text file
Skip,


I dont understand, this will place a zero in front of all imported data in that column?

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[SkipVought]

"...this will place a zero in front of all imported data in that column?"

Not all imported data. Has nothing to do with import. It has to do with NUMBERS in that column.

How many CHARACTERS do you want in this column in the file you want to export to the mainframe? Lets assume 8. Format that column, custom 00000000

Format ALL data courier. It's a FIXED PITCH FONT. Most others are not. Autofit the columns or chage the width to meet your specifications. Then SaveAs a .prn to get a Fixed width text file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}