Google Sheets – How to Import Text File As-Is

google sheets

I'm importing a text file with some columns being 000000, 0000, 000000000. Google Docs converts them all to 0 – treating them as numbers. How do I make the columns contains exactly the string that was in the text file?

Best Answer

The column data 000000, etc. is being seen as a number and consequently the leading zeros are being removed. ~~Unfortunately, AFAIK, there is no way in Spreadsheet to format a number with leading zeros.~~ In the old Google Spreadsheet there was no way to format a number with leading zeros. However, as mentioned by pnuts in comments, in the new Google Sheets you can specify a "Custom number format..." under Format > Number > More Formats. Specifying a format string such as 0000 will format a number to 4 digits with leading zeros. The formatting would need to be done after the data is imported.

However, if these data values are intended to be strings, then you will need to modify the source data by prefixing the data values with ' (an apostrophe) in order to force a string value. For example:

'000000,'0000,'000000000

Related Solutions

Google Sheets – How to Escape a Double-Quote in a TSV File for Import

Try four double-quotes, e.g.

""""

According to Wikipedia there is no reliable standard, but some attempts have been made.

A (double) quote character in a field must be represented by two double quote characters.

Google Sheets – Text Cell Formatting Question

I've created this little script to format the input:

function SCHANGE(array) {
  if(typeof(array) == 'number') {
    var number = array.toString();
  } else {
    var number = array.replace(/\D/g,'');
  }

  if(number.length != 12) {
    return "<> 12"
  } else {
    var first = number.substring(0,4);
    var mid = number.substring(4,8);
    var last = number.substring(8,12);
    var formText = first + " - " + mid + " - " + last;
  }
  return formText;
}

The script validates the input and converts it, checks whether 12 digits are given and returns the outcome. I've prepared an example file for you to play around with: formatted text.

Best Answer

Related Solutions

Google Sheets – How to Escape a Double-Quote in a TSV File for Import

Google Sheets – Text Cell Formatting Question

Related Topic