Konwersja tekstu na kolumny w arkuszach kalkulacyjnych Google

Jak przeprowadzić konwersję tekstu na kolumny w arkuszach kalkulacyjnych Google?

Na przykład mam następujący ciąg danych w jednej komórce:

5,233,6,2,6,7,2,2,6,6

Chcę go rozdzielić za pomocą separatora przecinków na kolumny.

Edycja: Zmieniłem zaakceptowaną odpowiedź na taką, która nie korzysta z Google Apps Scripting, ponieważ Google wydaje się być bardzo zajęty osłabieniem swoich możliwości.

Wykona to następująca formuła; tekst do kolumny:

A1=5,233,6,2,6,7,2,2,6,6
A2=SPLIT(A1;",")

I następny; tekst do wiersza:

A1=5,233,6,2,6,7,2,2,6,6
A2=TRANSPOSE(SPLIT(A1;","))

AKTUALIZACJA 03-02-2013
Jeśli podzielisz wynik A1i wkleisz wartości, da to ten sam wynik, co wszystkie wiersze kodu użyte w odpowiedzi PO. Wypróbowałem to również przy pomocy Google Apps Script i oto, co stworzyłem: tekst do kolumny

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();

  sh.getRange(row,col+1,1,sCell.length).setValues([sCell]);  
}

Po prostu używam wbudowanej funkcji podziału, aby podzielić wynik i dodać go do arkusza, nic więcej i nic mniej.

Skorzystaj ze skryptów Google Apps, aby rozszerzyć interfejs użytkownika

Tekst do kolumn to bardzo przydatna funkcja i jeden z powodów, dla których wielu użytkowników Arkusza Google wraca do korzystania z programu Excel. Dopóki Google nie zdecyduje się oficjalnie wesprzeć tej funkcji, to rozwiązanie może być używane jako polifill w celu dodania funkcjonalności.

Oto kod:

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(null);
  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);
  textToColumns(separator);
}

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

// 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);
    return;
  }  
  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));
    return;
  } 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; }
      values.push(rowValues);
    }
    // set all values at once (padding required because setValues doesn't accept jagged 2d arrays)
    padRow(values, cols);
    ss.getRange(firstRow,col+1,values.length,cols).setValues(values);
  }
}

// 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;
  ss.getRange(firstRow,col,values.length,1).setValues(values);
}

Zapisz i zamknij edytor skryptów. Następnie odśwież arkusz kalkulacyjny. Załadowanie zajmie sekundę, ale po pasku „Pomoc” na pasku narzędzi powinno pojawić się menu „Zaawansowane”.

Stosowanie:

• Wybierz komórki zawierające wartości do podzielenia
• Wybierz Zaawansowane → Tekst do kolumn

to jest to! Możesz także dokonać podziału za pomocą niestandardowego separatora (poprzez „Tekst na kolumny (niestandardowe)”) i odwrócić proces (poprzez „Kolumny na tekst”).

Użyłem funkcji podziału i działała idealnie, ponieważ używa również formuły kontynuacji i wykonuje dokładnie tłumaczenie tekstu z kolumny A na kolumny BCDE z odpowiednimi odstępami.

Mój przykład:

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

Wynik w B1 to tekst 1. Wynik w C1 to tekst2 Wynik w D1 to data1 Wynik w E1 to liczba1.

Dba o format, ponieważ data została przeliterowana na 1 czerwca i przetłumaczona na 01/06.

Wzory, że rozstali stosowane w komórkach BCDE gdzie CONTINUE(B1; 1; 2), CONTINUE(B1; 1; 3), CONTINUE(B1; 1; 4). Cała ta część została utworzona automatycznie.

Konwertuj dane csv na tsv (wartości rozdzielane tabulatorami).
Wklej to.

Naprawdę podoba mi się odpowiedź Evana przy użyciu Apps Script i dokonałem niewielkiego ulepszenia: dodałem obsługę dopasowania ograniczników wyrażeń regularnych. Do menu Entries in onOpen dodałem:

menuEntries.push({
  name: "Text to columns (regular expression separator)",
  functionName:"textToColumnsRegExp"
});

I dodała funkcję, do której istnieje odwołanie:

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

Żadne inne zmiany nie były wymagane, ponieważ Evan używa String.prototype.split JavaScript, który przyjmuje jako ciąg ogranicznik albo ciąg RegExp. Więc uwielbiam Evana!

Ponadto, po użyciu funkcji SPLIT, która zapewni tablicę zawierającą oddzielone wartości, możesz wyizolować określony wiersz lub kolumnę z tej tablicy za pomocą funkcji INDEKS:

=index(split(importXML("https://www.google.com/search?q=stackexchange","//div[@id='resultStats']/text()")," "),1,2,1)

Może być przydatny na przykład do wyszukiwania liczby wyników dla zapytania Google

Wydaje się, że istnieje element menu, który może w tym pomóc, w: Dane → Podziel tekst na kolumny ...

1. Kliknij komórkę jednym kliknięciem i wklej swoje dane.

   Pojawi się w wielu wierszach, ale tylko w jednej kolumnie.

2. Pozostawiając podświetlone nowo wypełnione komórki, przejdź do menu Dane → Podziel tekst na kolumny ...

   Jeśli aplikacja zdołała automatycznie wykryć separatory, gratulacje: gotowe!

   W przeciwnym razie pojawi się mały widget z pytaniem o separator, który chcesz podzielić.

   Uwaga: ten widget może pojawić się w dolnej części okna, co utrudnia jego znalezienie!

3. Użyj widżetu, aby wybrać separator, w którym dane będą rozdzielane: przecinkiem, średnikiem, kropką, spacją lub niestandardową.

4. Jeśli chcesz podzielić na Tabs:

A odpowiednikiem columnToText byłoby użycie =JOIN(delim, array)formuły. Na przykład =JOIN(",", A1:A10)spowodowałoby to skonkatenowanie ciągu wartości z komórek A1 do A10.

W nowej wersji Arkuszy kalkulacyjnych Google skrypty aplikacji są teraz przestarzałe .

Można użyć Elektronarzędzia z Google Add-on Gallery i skorzystać z podziału funkcji. To dobrze działa.