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.

  • Why have you added the google-apps-script tag? Did on of the answers fit? Jan 31, 2013 at 15:08
  • 1
    @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 googlegooru.com/text-columns-google-spreadsheets. Jan 31, 2013 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. Feb 1, 2013 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. Feb 1, 2013 at 18:53
  • 1
    @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. Sep 8, 2015 at 18:05

9 Answers 9


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.

  • Nice... How do you call it without extending the UI. Can you add a quick example demonstrating usage? May 6, 2015 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').

  • 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. Feb 8, 2013 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. Feb 8, 2013 at 17:11
  • I don't see it in the Script Gallery. Is it still there? Sep 12, 2014 at 19:04
  • 1
    @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. Dec 16, 2014 at 21:11
  • There are a few edge cases where this isn't working. 1,421,873,190,017,370,000,000,000 1.42E+24 That csv line should have been split into 9 columns, but it was split into just 1. Jul 25, 2015 at 18:05

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.


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

  • 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, 2015 at 16:48
  • 1
    Worked perfectly for me with a simple ctrl+v. It seems by far to be the simplest solution!
    – Didier L
    Jan 11, 2016 at 15:28

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!


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("https://www.google.com/search?q=stackexchange","//div[@id='resultStats']/text()")," "),1,2,1)

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


There appears to be a menu item that can help with this, at: Data → Split text to columns...

  1. Single click on a cell and paste your data.

    It will appear over multiple rows but only one column.

  2. Leaving the newly populated cells highlighted, go to the menu Data → Split text to columns...

    If the app managed to auto-detect your separators, then congratulations: you are done!

    Otherwise, a little widget will appear asking what separator you want to split on.

    Note: this widget may appear near the bottom of the window making it a little hard to find!

  3. Use the widget to choose which separator your data is delimited by: Comma, Semicolon, Period, Space or Custom.

  4. If you wanted to split on Tabs:

You can't!


I wrote this function to split a particular column.

function SplitColumnValues(sheet_name, column_index, delimiter) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheet_name);
  var lastRow = sheet.getLastRow();

  var range = sheet.getRange(1,column_index,lastRow-1, 1);

The function takes sheet name, Column that you want to split and delimiter.

First, get the reference sheet

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheet_name);

next line will get the number of rows that are in the column that needs to be split

var lastRow = sheet.getLastRow();

next get the range(i.e. the column that needs to be split) and then split that range using 'splitTextToColumn'

var range = sheet.getRange(1,column_index,lastRow-1, 1);

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.

  • 1
    That's true, but the OP said it had values (comma separated) in one cell. Sep 8, 2013 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, 2015 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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