-1

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

E1  | F1  | G1
....

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.

3
  • 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 '20 at 1:50
  • Would you please explain how the numeric suffix is determined?
    – Tedinoz
    Jul 2 '20 at 6:43
  • The data is input into the row in three categories: Name, Type, and Info, and then repeats. So the 45 columns are just going Name, Type, Info, Name (new group), Type, Info, etc etc
    – Adam Park
    Jul 3 '20 at 18:26
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

1
  • Refer my update.
    – Tedinoz
    Jul 6 '20 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.