retaining trailing zeros (XL 97 sr2 on Win 2000/NT

Topic

New Reply

I have a CSV file that contains some numbers that have trailing zeros e.g.
24.1,15.0,13.1

XL being helpful can read csv files without any problem so when I open the csv file I get an XL sheet holding each number in separate cell, trouble is XL (being less helpful) ditches the trailing zeros and I get :

24.1

15

13.1

I have tried a less direct approach:
– create a blank sheet, format all the cells to text
– open the csv file in notepad then copy and paste the data into a single column
– then use the text to columns feature making sure I select ‘text’ as the column data format
but still the trailing zero disappears.

I know mathematically the trailing zeros are not significant but I must at least capture them as text before I let XL loose to do its sums. Any ideas how I can get XL to import the csv file and treat everything it finds as text even if the variables between the commas are numbers?

stuck

Reply | Quote

Replies

Have you tried formatting the cells to “Number” and setting the decimal place to 1?

Reply | Quote

Sorry, should have explained that bit too. Using a fixed number of dps is not an option. The trailing zero(s) could be on the end of a value of any length.

stuck

Reply | Quote

Sorry, should have explained that bit too. Using a fixed number of dps is not an option. The trailing zero(s) could be on the end of a value of any length.

stuck

Reply | Quote

rename the file extension to txt and then use the open/import functionality to change the fields to text.

Reply | Quote

Should have made this clear as well. Doing this is the same as using the text to columns feature as I explained in my original post. While the final values in the sheet are text the trailng zero is lost.

stuck

Reply | Quote

Should have made this clear as well. Doing this is the same as using the text to columns feature as I explained in my original post. While the final values in the sheet are text the trailng zero is lost.

stuck

Reply | Quote

rename the file extension to txt and then use the open/import functionality to change the fields to text.

Reply | Quote

An idea (Not completely worked out any details) might be to FORCE excel to not convert to a value. excel for ALL numbers uses 15 digits of precision.15 = 15.0 = 15.00 is actually 15.0000000000000. XL can NOT differentiate between any of those numbers. It can differentiate if the explicit FORMAT of the numbers ONLY.

If you were to edit your CSV (in notepad or word) to add a “sticky-space” (ascii 160 = 0160) either before or after the “number” excel will NOT convert it to a number and it will remain as TEXT. You can later use substitute to replace the sticky space with a null to get the value.

Steve

Reply | Quote

It may not be fully worked out but it certainly is an idea…

I am aware that XL works to 15 digts and when I let it loose to do the sums then I am quite happy for it to drop the trailing zeros but I must retain them as text first. So, tweaking the CSV file by adding a sticky space or a single opening quote to before a number does make XL treat it as text and as you say, it is easy subsequently to remove such markers. Perhaps the real fix is to get the format of the variables in the CSV file changed.

Meanwhile, I have discovered tht if I open the csv file in word and convert the text to a table using the commas as the delimiter I can then paste special the table as text into an XL sheet formated as text and I get the desired effect. For now that will do, but I’d prefer a one stop shop XL solution.

(thinking rather than) stuck

Reply | Quote

It may not be fully worked out but it certainly is an idea…

I am aware that XL works to 15 digts and when I let it loose to do the sums then I am quite happy for it to drop the trailing zeros but I must retain them as text first. So, tweaking the CSV file by adding a sticky space or a single opening quote to before a number does make XL treat it as text and as you say, it is easy subsequently to remove such markers. Perhaps the real fix is to get the format of the variables in the CSV file changed.

Meanwhile, I have discovered tht if I open the csv file in word and convert the text to a table using the commas as the delimiter I can then paste special the table as text into an XL sheet formated as text and I get the desired effect. For now that will do, but I’d prefer a one stop shop XL solution.

(thinking rather than) stuck

Reply | Quote

I now have a CSV file where the commas are followed by an apostrophe/single quote mark (‘) and everything is wonderful! Well no, actually I lied hence I’m back here again.

What is the VBA equivalent of the SUBSTITUTE worksheet function? Do I just use Application.WorksheetFunction.Substitute or is there a more elegant trick?

stuck

Reply | Quote

I usually use:
Application.WorksheetFunction.Substitute

since VB has no equivalent

You could also use something like (str is the value to convert)
val(mid(str,2,len(str)-1))
to extract all but the apostrophe
Steve

Reply | Quote

I usually use:
Application.WorksheetFunction.Substitute

since VB has no equivalent

You could also use something like (str is the value to convert)
val(mid(str,2,len(str)-1))
to extract all but the apostrophe
Steve

Reply | Quote

Hi stuck,

If your csv data has a fixed set of formats (eg by column), it should be a fairly straight-forward matter to format the columns to show fixed decimals etc after importing.

Conversely, if you have a spreadsheet with rows/columns/cells already formatted the way you want the imported data to appear, and you can rely on the import file’s structure matching that layout, you could import the data using a macro like the attached (untested), which I modified from an old MS KB example. Note that running the macro could take a lot longer than simply opening a csv file.

If your import data are not suffciently structured to use either of the above, you’d need to modify the code further to ‘interpret’ the formatting of each comma-separated value so that it could be applied on a cell-by-cell basis. You’ll see a suggested insertion point about 1/2 way down.

Cheers

PS: You’ll need to delete the line for creating a new workbook, which is what the macro does.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi stuck,

If your csv data has a fixed set of formats (eg by column), it should be a fairly straight-forward matter to format the columns to show fixed decimals etc after importing.

Conversely, if you have a spreadsheet with rows/columns/cells already formatted the way you want the imported data to appear, and you can rely on the import file’s structure matching that layout, you could import the data using a macro like the attached (untested), which I modified from an old MS KB example. Note that running the macro could take a lot longer than simply opening a csv file.

If your import data are not suffciently structured to use either of the above, you’d need to modify the code further to ‘interpret’ the formatting of each comma-separated value so that it could be applied on a cell-by-cell basis. You’ll see a suggested insertion point about 1/2 way down.

Cheers

PS: You’ll need to delete the line for creating a new workbook, which is what the macro does.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I now have a CSV file where the commas are followed by an apostrophe/single quote mark (‘) and everything is wonderful! Well no, actually I lied hence I’m back here again.

What is the VBA equivalent of the SUBSTITUTE worksheet function? Do I just use Application.WorksheetFunction.Substitute or is there a more elegant trick?

stuck

Reply | Quote

An idea (Not completely worked out any details) might be to FORCE excel to not convert to a value. excel for ALL numbers uses 15 digits of precision.15 = 15.0 = 15.00 is actually 15.0000000000000. XL can NOT differentiate between any of those numbers. It can differentiate if the explicit FORMAT of the numbers ONLY.

If you were to edit your CSV (in notepad or word) to add a “sticky-space” (ascii 160 = 0160) either before or after the “number” excel will NOT convert it to a number and it will remain as TEXT. You can later use substitute to replace the sticky space with a null to get the value.

Steve

Reply | Quote