Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Leading Zeros | Excel Discussion (Misc queries) | |||
how to keep all leading zeros when stripping dashes from txt num | Excel Discussion (Misc queries) | |||
Stop Excel from stripping out leading zeros when saving as CSV | Excel Discussion (Misc queries) | |||
Excel Leading Zeros | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |