Stop Excel From Formating Text Starting with A Zero
 

This is driving me nuts!

I have a script that dumps a text file into Excel. The text file
contains some data such as the following.

Name Rev State Last First
Date
06700332 2 330 Clysdale Ron 11/20/2006 21:59

What happens though in Excel is that the 06700332 is changed to
6700332 automatically. I can't get it to stop it. I've tried
formating the column as text with no luck. Perpaps there is something
that I can programatically stick in the front of the zero to tell
Excel it's not a number. I don't know.

Can you help?
Andy

Stop Excel From Formating Text Starting with A Zero
 

Perhaps there is something
that I can programmatically stick in the front of the zero to tell
Excel it's not a number.

Indeed there is, try an apostrophe '

activecell.value = "'" & "0123456"

Otherwise format the cell as Text

Regards,
Peter T

"Andy" wrote in message
...
This is driving me nuts!

I have a script that dumps a text file into Excel. The text file
contains some data such as the following.

Name Rev State Last First
Date
06700332 2 330 Clysdale Ron 11/20/2006 21:59

What happens though in Excel is that the 06700332 is changed to
6700332 automatically. I can't get it to stop it. I've tried
formating the column as text with no luck. Perpaps there is something
that I can programatically stick in the front of the zero to tell
Excel it's not a number. I don't know.

Can you help?
Andy

Stop Excel From Formating Text Starting with A Zero
 

On Mar 11, 5:32*pm, "Peter T" <peter_t@discussions wrote:
Perhaps there is something
that I can programmatically stick in the front of the zero to tell
Excel it's not a number.

Indeed there is, try an apostrophe '

activecell.value = "'" & "0123456"

Otherwise format the cell as Text

Regards,
Peter T

"Andy" wrote in message

...



This is driving me nuts!

I have a script that dumps a text file into Excel. *The text file
contains some data such as the following.

Name * * * Rev * * * * * State * * * *Last * * * * *First
Date
06700332 2 330 Clysdale Ron 11/20/2006 21:59

What happens though in Excel is that the 06700332 is changed to
6700332 automatically. *I can't get it to stop it. *I've tried
formating the column as text with no luck. *Perpaps there is something
that I can programatically stick in the front of the zero to tell
Excel it's not a number. *I don't know.

Can you help?
Andy- Hide quoted text -

- Show quoted text -

Hi Peter

This works sort of. The leading zeros are there now but I also get
the single quote showing as well.

Now in Excel I have the values (for example)

'06650077
'06700219
'06700306

I would have expected the single quote to disappear. Any thoughts?

Andy

Stop Excel From Formating Text Starting with A Zero
 

I had this in the past (single quote failed as well).
The solution that worked for me as follows:
Before dumping the data, define the format of the entire column (e.g.
E:E) as text - manually or with VBA.
Then dump your data.

Stop Excel From Formating Text Starting with A Zero
 

I think this is probably what you are looking for. I have an add-in
that uses very large numbers preceded with a 0. So, not only does
excel convert them into scientific notation, but it removes the zero.

With .Range("E:E")
.NumberFormat = "0"
.Value = .Value
End With

I had to use .Value = .Value because this particular format change
doesn't always apply until the cells are re-evaluated.

Stop Excel From Formating Text Starting with A Zero
 

What's your numberformat

Regards,
Peter T

"Andy" wrote in message
...
On Mar 11, 5:32 pm, "Peter T" <peter_t@discussions wrote:
Perhaps there is something
that I can programmatically stick in the front of the zero to tell
Excel it's not a number.

Indeed there is, try an apostrophe '

activecell.value = "'" & "0123456"

Otherwise format the cell as Text

Regards,
Peter T

"Andy" wrote in message

...



This is driving me nuts!

I have a script that dumps a text file into Excel. The text file
contains some data such as the following.

Name Rev State Last First
Date
06700332 2 330 Clysdale Ron 11/20/2006 21:59

What happens though in Excel is that the 06700332 is changed to
6700332 automatically. I can't get it to stop it. I've tried
formating the column as text with no luck. Perpaps there is something
that I can programatically stick in the front of the zero to tell
Excel it's not a number. I don't know.

Can you help?
Andy- Hide quoted text -

- Show quoted text -

Hi Peter

This works sort of. The leading zeros are there now but I also get
the single quote showing as well.

Now in Excel I have the values (for example)

'06650077
'06700219
'06700306

I would have expected the single quote to disappear. Any thoughts?

Andy