REGEX: Transpose each word from a google sheet cell and put them one under the other (rows)-CodePudding

I need to extract each word (phrase) within a cell in google sheets and put each one under the other in a column (row for each one).

I have a regex code that works when testing it, but I cannot do it work in google sheet the same code. Any ideas?

CodePudding user response：

Update: this answer does not meet all requirements as it puts all results in one cell.

The ASCII character numbers need to be used instead of \r\n that you would expect from other tools.

Carriage return has number 13 and line feed has number 10:

=REGEXREPLACE(A1, "\s", CONCAT(CHAR(13), CHAR(10)))

CodePudding user response：

You don't really need regex for this. You can use Transpose and split methods of google sheets. Example: In sheets in A1 put your text then in B1 copy this =TRANSPOSE(SPLIT(A1," "))

CodePudding user response：

You can just do

=SUBSTITUTE(A1," ",char(10))

=transpose(split(A1," "))