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

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).

enter image description here

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

regexgoogle-sheetsgoogle-sheets-formulaspreadsheetregex-replace


Solution 1:[1]

You can just do

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

enter image description here

or

=transpose(split(A1," "))

enter image description here

Solution 2:[2]

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," "))

Solution 3:[3]

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)))

screenshot

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mike Steelson
Solution 2 Nik Owa
Solution 3

Related Questions