Stopping Excel from stripping off leading zeros?
 

I make many spreadsheets that contain a CUSIP column, which can have
leading zeros. Excel helpfully strips these off, rendering them
unreadable.

Normally I address this by putting a quote in front of the number to
force it to be a string. However, I notice that the quote is actually
exported if you save it as CSV.

So I can't use a Format to do this because that gets killed off in the
CSV form and the zeros get stripped, and I can't use the quote because
it doesn't! Any suggestions?

Stopping Excel from stripping off leading zeros?
 

I assume you mean you prefix with an apostrophe.

Depending on what you are doing overall, you may find it helps first to
format the cells as Text

rng.NumberFormat = "@"

Regards,
Peter T


"Maury Markowitz" wrote in message
...
I make many spreadsheets that contain a CUSIP column, which can have
leading zeros. Excel helpfully strips these off, rendering them
unreadable.

Normally I address this by putting a quote in front of the number to
force it to be a string. However, I notice that the quote is actually
exported if you save it as CSV.

So I can't use a Format to do this because that gets killed off in the
CSV form and the zeros get stripped, and I can't use the quote because
it doesn't! Any suggestions?

Stopping Excel from stripping off leading zeros?
 

I assume you mean you prefix with an apostrophe.

Depending on what you are doing overall, you may find it helps first to
format the cells as Text

rng.NumberFormat = "@"

Regards,
Peter T


"Maury Markowitz" wrote in message
...
I make many spreadsheets that contain a CUSIP column, which can have
leading zeros. Excel helpfully strips these off, rendering them
unreadable.

Normally I address this by putting a quote in front of the number to
force it to be a string. However, I notice that the quote is actually
exported if you save it as CSV.

So I can't use a Format to do this because that gets killed off in the
CSV form and the zeros get stripped, and I can't use the quote because
it doesn't! Any suggestions?

Stopping Excel from stripping off leading zeros?
 

I do not believe excel strips the leading zero's when saving to CSV. I'm
using version 2000 (poor me). When I open the CSV in notepad, it shows the
zeros.

I used both the Edit....Cells...Format and the TEXT(a1, "0000000") option.
In both cases the zeros were inside the CSV.

"Maury Markowitz" wrote:

I make many spreadsheets that contain a CUSIP column, which can have
leading zeros. Excel helpfully strips these off, rendering them
unreadable.

Normally I address this by putting a quote in front of the number to
force it to be a string. However, I notice that the quote is actually
exported if you save it as CSV.

So I can't use a Format to do this because that gets killed off in the
CSV form and the zeros get stripped, and I can't use the quote because
it doesn't! Any suggestions?