Sign up ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

How do you do a text-to-columns conversion in Google Spreadsheets?

For example, I have the following data string in one cell:


I want to break it apart by the comma separator into columns.

Edit: I changed the accepted answer to one that doesn't use Google Apps Scripting because Google seems hell-bent on nerfing it's ability.

share|improve this question
Why have you added the google-apps-script tag? Did on of the answers fit? – Jacob Jan Tuinstra Jan 31 '13 at 15:08
@JacobJanTuinstra Because the capability can be added via Google Apps Scripting. I ended up solving my own problem by writing a 'Text To Columns' script that can now be found in the Script Gallery. Googlegooru also posted a video tutorial demonstrating its usage here – Evan Plaice Jan 31 '13 at 18:16
What's the difference to the solution already provided by Google Spreadsheet: SPLIT. That the values are set? You can paste values you know. – Jacob Jan Tuinstra Feb 1 '13 at 9:19
@JacobJanTuinstra In your first solution try copying B2 to B3. The result cells contain continuation formulas, not raw data. The cells outputted from the script contain the actual raw data so they can be copied/moved without any issues. The intent of this question is to find an equivalent to Excel's 'Text to Columns' feature. Until, Google officially adds support the script is as close as it's going to get. – Evan Plaice Feb 1 '13 at 18:53
@Rubén So much for stability. I checked earlier and was under the impression that scripting was gone altogether. Turns out, they only killed the script gallery. I'm going with the answer you recommended because it's the simplest scriptable solution. Thanks for the feedback. – Evan Plaice Sep 8 at 18:05

8 Answers 8

up vote 11 down vote accepted

The follow formula will do just that; text-to-column:


And the next; text-to-row:


UPDATE 03-02-2013
If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as well with Google Apps Script and this is what I created: text to column

function mySplit() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sh.getActiveCell().getValues()[0];
  var sCell = cell[0].split(",");
  var row = sh.getActiveCell().getRowIndex();
  var col = sh.getActiveCell().getColumnIndex();


I simple use the build-in split function to split the result and add it it to the sheet, nothing more and nothing less.

share|improve this answer
Nice... How do you call it without extending the UI. Can you add a quick example demonstrating usage? – Evan Plaice May 6 at 19:10

Leverage Google Apps Scripting to extend the UI

Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using Excel. Until, Google decides to officially support the feature, this solution can be used as a polyfill to add in the functionality.

Here's the code:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({ name:"Text to columns", functionName:"textToColumns" });
  menuEntries.push({ name:"Text to columns (custom separator)", functionName:"textToColumnsCustom" });
  menuEntries.push({ name:"Columns to Text", functionName:"columnsToText" });
  menuEntries.push({ name:"Columns to Text (custom separator)", functionName:"columnsToTextCustom" });
  ss.addMenu("Advanced", menuEntries);

function textToColumnsCustom() {
  var separator = Browser.inputBox("Text to column","Enter the the separator",Browser.Buttons.OK);

function columnsToTextCustom() {
  var separator = Browser.inputBox("Column to text","Enter the the separator",Browser.Buttons.OK);

// Expands a single cell of CSV formatted text to multiple columns
function textToColumns(separator) {
  var sep = typeof(separator) !== 'undefined' ? separator : ',';
  var ss = SpreadsheetApp.getActiveSheet(); 
  var r = ss.getActiveRange();
  // check that only one column was selected
  var col = r.getColumn(); 
  if(col !== r.getLastColumn()) {
    Browser.msgBox("Error", "Invalid selection, too many columns.", Browser.Buttons.OK);
  var firstRow = r.getRow();
  // short cut the one row selection
  if(firstRow === r.getLastRow()) {
    var values = r.getValues().toString().split(sep);
    ss.getRange(firstRow,col+1,1,values.length).setValues(new Array(values));
  } else {
    var rows = r.getValues();
    var values = [];
    var cols = 0;
    for(var i = 0, len = rows.length; i < len; i++) {
      var rowValues = rows[i].toString().split(sep); 
      var rowValuesLen = rowValues.length;
      if(cols < rowValuesLen) { cols = rowValuesLen; }
    // set all values at once (padding required because setValues doesn't accept jagged 2d arrays)
    padRow(values, cols);

// Pads a row with empty values to the specified length
function padRow(array, length) {
  for(var i = 0; i < array.length; i++) {
    var arrLen = array[i].length;
    if(arrLen < length) {
      var padLen = length - arrLen;
      var padding = new Array(padLen);
      array[i].push.apply(array[i], padding);
      for(var j = 0, len = array[i].length; j < len; j++) {
        if(typeof(array[i][j]) === 'undefined') {
          array[i][j] = "";
  return array;

function columnsToText(separator) {
  var sep = typeof(separator) !== 'undefined' ? separator : ',';
  var ss = SpreadsheetApp.getActiveSheet(); 
  var r = ss.getActiveRange();
  var col = r.getColumn();  
  var firstRow = r.getRow();
  var rows = r.getValues();
  var values = [];
  for(var i = 0, len = rows.length; i < len; i++) {
    var value = rows[i].join(sep);
    values[i] = [value];
  col -= 1;

Save and close the script editor. Then, refresh the spreadsheet. It'll take a second to load but you should see a menu called 'Advanced' popup after 'Help' in the toolbar.


  • Select the cell(s) containing the values to split
  • Select AdvancedText to columns

That's it. You can also do a split using a custom delimiter (via 'Text to Columns (Custom)') and reverse the process (via 'Columns to Text').

share|improve this answer
I just installed this from the Scripts Gallery and it wasn't working. I fixed it by going to Tools → Script Manager → Edit button and adding a semi-colon after each function definition's closing brace. Thanks for the script. – bob esponja Feb 8 '13 at 11:19
@bobesponja Thanks for the heads-up. I know there's a bug with Google Scripts where event triggers from imported scripts don't register properly. To fix it, just add the onOpen trigger manually. – Evan Plaice Feb 8 '13 at 17:11
I don't see it in the Script Gallery. Is it still there? – espertus Sep 12 '14 at 19:04
@espertus Looks like Google got rid of the script gallery in favor of their new add-ons thing. Just copy the code above into a script, close, then reopen the document and it should work. – Evan Plaice Dec 16 '14 at 21:11
@Rubén Done. Thanks for pointing that out. – Evan Plaice Sep 8 at 18:03

I used the split function, and it worked perfectly, since it uses the continue formula as well and it does exactly the translation from text on column A to columns BCDE with proper spacing.

My example:

Cell A1= text1, text2, date1, number1
Cell B1= split(A1,",")

Outcome in B1 is text1. Outcome in C1 is text2 Outcome in D1 is date1 Outcome in E1 is number1.

It takes care of the format, since the date was spelled as 1-jun, and it translated to 01/06.

The formulas that split used in cells BCDE where CONTINUE(B1; 1; 2), CONTINUE(B1; 1; 3), CONTINUE(B1; 1; 4). All of this part was created automatically.

share|improve this answer
Is this a comment or a solution? – Jacob Jan Tuinstra Aug 28 '13 at 17:22

I really like Evan's answer using Apps Script, and made a small improvement: adding support for regular expression delimiter matching. To the menuEntries in onOpen I added:

  name: "Text to columns (regular expression separator)",

And added the referenced function:

function textToColumnsRegExp() {
  var separator = Browser.inputBox(
    "Text to column",
    "Enter the regular expression for the separator ",
  if (separator) {
    textToColumns(new RegExp(separator));

No other changes were required because Evan is using Javascript's String.prototype.split, which accepts either a string or a RegExp object as the delimiter. So kudos the Evan!

share|improve this answer

Furthermore, after using the SPLIT function which will provide the array containing the separated values, you can isolate a specific row or column from this array with the INDEX function:

=index(split(importXML("","//div[@id='resultStats']/text()")," "),1,2,1)

Can be useful for retrieving the number of results for a Google query for example

share|improve this answer

Convert your csv data into tsv (tab separated values).
Paste it then.

share|improve this answer
I just tried that with regular paste, did not work. Pasting with the browser's "Paste and match style" does work (Chrome/MacOS) – nhed Nov 10 at 16:48

And an equivalent of columnToText would be to use the =JOIN(delim, array) formula. For example, =JOIN(",", A1:A10) would result it concatenated string of values from cells A1 thru A10.

share|improve this answer
That's true, but the OP said it had values (comma separated) in one cell. – Jacob Jan Tuinstra Sep 8 '13 at 12:21
useful info, but probably best posted as a comment under one of the answers to split I guess. Since this doesn't answer the original question. – David Jul 22 at 1:11

With the new version of Google Spreadsheets, Google App Scripts is now deprecated.

You can use the Power Tools of the Google Add-on Gallery and use the Split function. It works well.

share|improve this answer
GAS isn't deprecated. It's the script gallery that's being replaced by the Add-on store !! – Jacob Jan Tuinstra Jul 27 at 19:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.