import CSV with a text field starting with 0

  • Thread starter Thread starter rlewing
  • Start date Start date
R

rlewing

Hi,

I'm trying to read a (java generated) CSV file into excel
which has a field containing a text which often begins
with character "0" (zero) or with character "+". Excel
seems to understand these text values as numbers and
strips the leading zeros and plus signs. If I add a
'-sign as first character in the field to indicate that
this should be understood as text, it remains visible.

How to tell excel to treat this field as text?

I appreciate any help

Thanks
 
If you use the Import wizard it will offer you the opportunity to
determine what type of cells go in each column.
 
I'm answering this old post since I think it is STILL a problem that people have in 2011.... And perhaps many years from now.

CSV is a format that is easy to generate, and not proprietary to one application. Therefore I often provide a function "generate/download spreadsheet" that provides a CSV, which the user can then open in Excel (or Ooo, or whatever). I have the same problem with TEXT fields starting with a + or 0, and containing only digits: Excel converts them automatically to numbers - not what is wanted. Using the import function and defining 1 (or more columns) as text is time-consuming and frustrating, users are happy to just (double)click once.

Here is a suggested solution, which admittedly is an ugly hack, but might work, depending on your circumstances. The idea is to append some non-digit character after the value, thus turning it into a value that Excel recognises as text. To make the value still look the same, this char should be a space (no "ink"), BUT the normal space character (dec 32, spacebar) does NOT work for this, as Excel trims it off too and so still recognises a number. 2 other characters that I have tested are your normal tab char (\t, dec 9, tab key) or the non-breaking space (dec 160, HTML  ). Other space chars, as defined by Unicode, MAY or MAY NOT work, please test first (see http://en.wikipedia.org/wiki/Space_(punctuation)#Table_of_spaces).
 
Back
Top