visible zeros

[Ella]

How do i tell excel to display zeros. I already went into
tools/options/view/zero values. That didn't work. What i am trying to
do is extract data from a text file,open it in excel(i would love to
have it open and work in access,but that is a different help group) and
have the colums with zeros in front of the data to show. But for some
reason it's not, anybody knows what i can do?
thanks

[Jim Thomlinson]

Are the zeros the number zero or the charachter zero. To find out try
increasing the number of decimals. If you can't increase the decimals then
you do not have numbers you have the text character 0. If that is the case
then you can probably just do a find and replace on the 0 with a 0 and Excel
will do a conversion for you to make the character into a number.
--
HTH...

Jim Thomlinson


"Ella" wrote:

> How do i tell excel to display zeros. I already went into
> tools/options/view/zero values. That didn't work. What i am trying to
> do is extract data from a text file,open it in excel(i would love to
> have it open and work in access,but that is a different help group) and
> have the colums with zeros in front of the data to show. But for some
> reason it's not, anybody knows what i can do?
> thanks
>
>

[Ella]

Jim,
Thank you for your help.The character is a number zero. The problem
will still be there when i open another file to extract from text to
excel. Isn't there a streamline way of doing this in the process of
extraction? Somewhere in VB in excel?

[Kleev]

I assume you mean a number with a leading 0. Is this correct? Like a zip
code 06439? If so, you can format as text (or if it really is a zip code,
there is a built-in format for that, under Format/Special.) Or you could
format the cell with however many 0's you need. In the above case, 00000.

"Ella" wrote:

> How do i tell excel to display zeros. I already went into
> tools/options/view/zero values. That didn't work. What i am trying to
> do is extract data from a text file,open it in excel(i would love to
> have it open and work in access,but that is a different help group) and
> have the colums with zeros in front of the data to show. But for some
> reason it's not, anybody knows what i can do?
> thanks
>
>

[Ella]

yes it is a number with a leading 0. Format as text does display the
zero only if i go back into the column and type in the zero itself. Not
effecient because there are thousands of records. Any other
advise...pleassseee.

[Jim Thomlinson]

Sorry I misunderstood your question. So you have a number of cells with
numbers in them but the leading Zero's have been dropped. There are a couple
of chices here. If it is just a matter of how the numbers are displayed then
you can change the format of the cell with a custom format (Right Click ->
Format Cells -> Custom) to 0000000 (or how ever many zeros you need). If you
require the values in the cells to be text with the zero's padded up front
then you could use the Text function something like =Text(A1, "0000000"). If
you need this to be an automated solution via VB then you need to traverse
through the cells changing each cell. If that is what you require then let me
know and I will throw something together for you...

--
HTH...

Jim Thomlinson


"Ella" wrote:

> Jim,
> Thank you for your help.The character is a number zero. The problem
> will still be there when i open another file to extract from text to
> excel. Isn't there a streamline way of doing this in the process of
> extraction? Somewhere in VB in excel?
>
>

[voodooJoe]

ella -

you need to do this when you import the data.

if you import the value '0012345' as a 'number' or "General" format, XL
reads it as a number and drops the leading 0s. If you import as text, XL
will read it as a string. You can set this at import time - try walking
through it manually and importing a small comma deliminated text file to
see.

cheers - voodooJoe

"Ella" <[email protected]> wrote in message
news:[email protected]...
> yes it is a number with a leading 0. Format as text does display the
> zero only if i go back into the column and type in the zero itself. Not
> effecient because there are thousands of records. Any other
> advise...pleassseee.
>

[Ella]

Joe,

thanks for helping, unfortunately i did that before i got online and
that doesn't work. Any other advice i can try?
thanks
ella


voodooJoe wrote:
> ella -
>
> you need to do this when you import the data.
>
> if you import the value '0012345' as a 'number' or "General" format, XL
> reads it as a number and drops the leading 0s. If you import as text, XL
> will read it as a string. You can set this at import time - try walking
> through it manually and importing a small comma deliminated text file to
> see.
>
> cheers - voodooJoe
>
> "Ella" <[email protected]> wrote in message
> news:[email protected]...
> > yes it is a number with a leading 0. Format as text does display the
> > zero only if i go back into the column and type in the zero itself. Not
> > effecient because there are thousands of records. Any other
> > advise...pleassseee.
> >

[voodooJoe]

you should be able to force at import time, but if you can't -- perhaps
something like this?

(assumes all the numbers should be the same number of characters)

reads incorrectly formatted row
creates a mirror range using REPT and LEN to add the correct number of 0s
copy pastespecial values to original range
clear mirror range

cheers - voodooJoe

Sub AddLeading000s_EDITtoSUIT()
Dim BananaPatch As Range, MustangSally As Range

Set BananaPatch = Sheet1.Range("D3:D7")
With BananaPatch
Set MustangSally = Sheet1.Range("F3").Resize(rowsize:=.Rows.Count,
columnsize:=.Columns.Count)
End With
With MustangSally
.FormulaArray = "=REPT(""0"",5-LEN(" & rngSource.Address & "))" & "
& " & rngSource.Address
.Copy
BananaPatch.PasteSpecial xlPasteValues
.Clear
End With

End Sub

"Ella" <[email protected]> wrote in message
news:[email protected]...
> Joe,
>
> thanks for helping, unfortunately i did that before i got online and
> that doesn't work. Any other advice i can try?
> thanks
> ella
>
>
> voodooJoe wrote:
>> ella -
>>
>> you need to do this when you import the data.
>>
>> if you import the value '0012345' as a 'number' or "General" format, XL
>> reads it as a number and drops the leading 0s. If you import as text, XL
>> will read it as a string. You can set this at import time - try walking
>> through it manually and importing a small comma deliminated text file to
>> see.
>>
>> cheers - voodooJoe
>>
>> "Ella" <[email protected]> wrote in message
>> news:[email protected]...
>> > yes it is a number with a leading 0. Format as text does display the
>> > zero only if i go back into the column and type in the zero itself. Not
>> > effecient because there are thousands of records. Any other
>> > advise...pleassseee.
>> >
>