Splitting rows and moving to csv lines in Excel

Question

asked Feb 19, 2021 in Technique[技术] by 深蓝 (71.8m points)

I have data looking like this:

John
1231231234
[email protected]
Bob
1241241244
[email protected]

I would like to have it look like this

john, 1231231234, [email protected]
Bob, 1241241244, [email protected]

What's the easiest way to do so in Excel?

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Answer

深蓝 · Answer 1 · 2021-02-19T03:39:56+0000

Note this assumes 3 cells per as a pattern:

Office 365/2019:

=TEXTJOIN(", ",TRUE,INDEX(A:A,(ROW($ZZ1)-1)*3+1):INDEX(A:A,(ROW($ZZ1)-1)*3+3))

Older:

=IF(INDEX(A:A,(ROW($ZZ1)-1)*3+1)<>"",INDEX(A:A,(ROW($ZZ1)-1)*3+1) & ", " & INDEX(A:A,(ROW($ZZ1)-1)*3+2) & ", " & INDEX(A:A,(ROW($ZZ1)-1)*3+3),"")