-2

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?

user44
  • 682
  • 7
  • 20
Martin Hogg
  • 41
  • 2

3 Answers3

1

You can just do

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

enter image description here

or

=transpose(split(A1," "))

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
0

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

user44
  • 682
  • 7
  • 20
  • Thank you Wiktor! But I need each word in an independent row of the spreadsheet. Is there any way to do it with regex? I do it by Split+transpose, but I think regex is to much better (I have a lot of info in the spreadsheet) – Martin Hogg May 13 '22 at 21:19
  • If you would like to keep the `\s` for detection of different types of whitespace, you can combine both answers - first replace whitespace with single space using regex, and then split & transpose. – user44 May 13 '22 at 21:28
0

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

Nik Owa
  • 86
  • 1
  • 4