0

I am working with a google sheet that gets data from an email parser. Each time an email comes in, a single row is created, and fills in these columns:

B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ.

I'd like to have it output as:

B1 | C1 |D1

D1 | E1 |F1
....

AO1|AP1|AQ1

B2 | C2|D2
....

Is this possible? I've tried using this:

=FILTER({Sheet1!B:B,Sheet1!C:C,Sheet1!D:D;Sheet1!E:E,Sheet1!F:F,Sheet1!G:G;Sheet1!H:H,Sheet1!I:I,Sheet1!J:J;Sheet1!K:K,Sheet1!L:L,Sheet1!M:M;Sheet1!N:N,Sheet1!O:O,Sheet1!P:P;Sheet1!Q:Q,Sheet1!R:R,Sheet1!S:S;Sheet1!T:T,Sheet1!U:U,Sheet1!V:V;Sheet1!W:W,Sheet1!X:X,Sheet1!Y:Y;Sheet1!Z:Z,Sheet1!AA:AA,Sheet1!AB:AB;Sheet1!AC:AC,Sheet1!AD:AD,Sheet1!AE:AE;Sheet1!AF:AF,Sheet1!AG:AG,Sheet1!AH:AH;Sheet1!AI:AI,Sheet1!AJ:AJ,Sheet1!AK:AK;Sheet1!AL:AL,Sheet1!AM:AM,Sheet1!AN:AN;Sheet1!AO:AO,Sheet1!AP:AP,Sheet1!AQ:AQ},LEN(Sheet1!A:A,Sheet1!A:A,Sheet1!A:A))

But it was just based on another answer I saw on here, and I am sure that I am not applying the filter(range,len()) part correctly.

  • Welcome to Web Applications "But it was just a based on another answer..." add a link to the referred "another answer". – Rubén Jul 2 at 1:50
  • Your successful outcome shows D1 repeated at the end of row 1 and the start of row 2. Is this a typo? – Tedinoz Jul 2 at 5:42
  • Your sample comma separated list does not contain 45 values. There are only 42 entries. Is this intentional, or is it a typo? – Tedinoz Jul 2 at 6:16
  • Would you please explain how the numeric suffix is determined? – Tedinoz Jul 2 at 6:43
  • Sorry yes, the repetition of D1 was a typo. – Adam Park Jul 3 at 18:25
0

You have a cell containing exactly 45 comma-separated values. Your goal is to split those values, add a suffix for the row number, and then list the values in rows of three - that is, in 45 cells (3 columns wide, 15 rows deep).

Use this formula:

=ArrayFormula(TRIM(split(transpose(SPLIT(Query(transpose(split(REGEXREPLACE($C$2&",",",",""&$B$2&","),", ",1,1))&","&if(MOD(row(indirect((address($B$7,$B$6)&":"&address($B$9,$B$6))))-row(indirect(address($B$7-1,$B$6))),$B$3)=0,"|",""),,9^9),"|")),",")))


Example output

Screenshot


Props:

infoinspired.com: Split a Column into Multiple N Columns in Google Sheets

Other similar formulas:


UPDATE

Values are already in 45 columns; no initial split is required.

Revised formula

=arrayformula(trim(split(transpose(split(query(transpose(indirect(address($A$6,$A$8)&":"&address($A$6,$A$10-1)))&A6&","&if(MOD(row(indirect((address($A$22,$A$20)&":"&address($A$24,$A$20))))-row(indirect(address($A$22-1,$A$20))),$A$2)=0,"|",""),,9^9),"|")),",")))


Screenshot

| improve this answer | |
  • Sorry - i typed in query confusingly initially. My data is coming into columns B, C, D, E ... AQ etc. – Adam Park Jul 6 at 3:18
  • so initially there are rows with cells spanning 45 columns – Adam Park Jul 6 at 3:19
  • Refer my update. – Tedinoz Jul 6 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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