In Google Sheets, How to use a function to split columns and rows at the same time?

Question

I succeed when there was only one row.

=SPLIT("aaa|bbb|ccc", "|", false)

aaa	bbb	ccc

And I succeeded when there was only one column.

=TRANSPOSE(SPLIT("aaa
bbb
ccc", "
", false))

aaa
bbb
ccc

But, what to do when the original data looks like this?

="aaa|bbb|ccc
ddd|eee|fff
ggg|hhh|iii"

aaa	bbb	ccc
ddd	eee	fff
ggg	hhh	iii

user_11861218 · Answer

Here is a formula that should work for you:

=arrayformula(split(transpose(split("aaa|bbb|ccc ddd|eee|fff ggg|hhh|iii", " ")), "|"))

It splits columns first, and then rows.

Please let me know if you have any issues with this