copying spread words to one column

[roniexcel2020]

Hi

I teach basic excel and a student of mine asked me a question i did not know the answer to.

For some reason he has got an excel sheet with lots of words spread in over columns and rows, some with empty cells in between some some without and with no order. He would like to copy all the words to one column preferably with no empty cells in between. I know i can copy any row and column seperately but imagine he has data in many many rows and columns and it will take a lot of time. I enclose a sample of what i mean. Of course his files are much bugger with many words.

Thnak you for your answer

[#2]

@roniexcel2020 The easiest would be to use PowerQuery, though this goes beyond "basic Excel". Are you familiar with PQ?

Example attached.

[#3]

@roniexcel2020 

For example in A1:

=TEXTJOIN(" ",TRUE,B1:Z1)

Expand the range if the data extend beyond column Z.

This can be filled down.

[#4]

@roniexcel2020 

One more variant

is

=SORT(UNIQUE(INDEX(B18:D23,MOD(SEQUENCE(6*3,,0),6)+1,SORT(MOD(SEQUENCE(6*3,,0),3)+1))))