Convert a row into fix number of multiple rows and columns Google Sheets

Question

I have a row which look like this:

|Data A-1|Data A-2|Data A-3|Data A-4|Data A-5|Data A-6|Data B-1|Data B-2|Data B-3|Data B-4|Data B-5|Data B-6|Data C-1|Data C-2|Data C-3|Data C-4|Data C-5|Data C-6|

There are 5 columns that are related to each Data. I need to convert all the data in a row into fix rows and columns which look like this:

|Data A-1|Data A-2|Data A-3|Data A-4|Data A-5|Data A-6|
|Data B-1|Data B-2|Data B-3|Data B-4|Data B-5|Data B-6|
|Data C-1|Data C-2|Data C-3|Data C-4|Data C-5|Data C-6|

How can I achieve this in Google Sheets?

Example Sheet

Tanaike Tanaike · Accepted Answer · 2021-01-08T06:04:44

In your case, I thought that this thread might be able to be used.

Sample formula 1:

For the goal in your question, how about the following sample formula?

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A1:1),"(([\w\s\S]+?,){6})","$1@"),"@")),",")))

In this case, one row is used. So A1:1 is used as the range. But when you have several rows, please modify the range. And, the row is splitted by 6 column. So (([\w\s\S]+?,){6}) is used as the regex.
The flow of this formula is as follows.
1. Join all cell values by ignoring the empty cells using TEXTJOIN.
2. Put @ to the joined text value for 6 columns using REGEXREPLACE.
3. Split the text value with @ using SPLIT.
4. Transpose the splitted values using TRANSPOSE.
5. Split the each row with , using SPLIT.

Sample formula 2:

For your shared Spreadsheet, how about the following sample formula?

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@"),"@")),",")))

In this case, one row is used. So A8:8 is used as the range. But when you have several rows, please modify the range. And, the row is splitted by 8 column. So (([\w\s\S]+?,){8}) is used as the regex.

Note:

In this case, because of REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@"), when the characters are over 50,000, an error occurs. In that case, I would like to propose to use the Google Apps Script as the custom function. The sample script is as follows. Please copy and paste the following script to the script editor of Spreadsheet, and put =SAMPLE(A8:8, 8) to a cell when your shared Spreadsheet is used. In this case, the arguments of A8:8 and 8 are the range and the splitted number, respectively. By this, your goal can be achieved.
```
  const SAMPLE = (values, split) => values.flatMap(r => {
    const temp = [];
    while (r.length > 0) temp.push(r.splice(0, split));
    return temp
  });
```

References:

Added:

About your additional question by your comment as follows,

Is there any way to make the first column to be sorted?

how about the following sample formula?

Sample formula:

=SORT(ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A8:8),"(([\w\s\S]+?,){8})","$1@"),"@")),","))),1,TRUE)

In this case, the rows are sorted by the 1st column as the ascending order.

When you use the custom formula created by Google Apps Script, you can also use SORT as follows.

  =SORT(SAMPLE(A8:8, 8),1,TRUE)

Or, you can also use the following script. When you use this, please put =SAMPLE2(A8:8, 8) to a cell.

  const SAMPLE2 = (values, split) => values.flatMap(r => {
    const temp = [];
    while (r.length > 0) temp.push(r.splice(0, split));
    return temp.sort((a, b) => a[0] - b[0]);
  });