Create a list from an existing list in excel

[dkingsbury]

I have a table with non-numerical values that are being populated from an if/index formula.

For example, I have the following set of data:

Hello	This
	is
This
	my
Is	second
My	column
Table	of
With	data
	here
Spaces

The values above are being pulled from another table using a combination if/index function, therefore the blank cells still have a value in them (a formula).

I would like to create a new list from this list that keeps them all together. For example, I would like the new list to look something like this:

Hello

This

Is

My

Table

With

Spaces

This

is

my

second

column

here

Is there any way this is possible? I feel like I've been able to do this before with a macro possibly, but I can't seem to locate the work I had previously done this with.

Any help is greatly appreciated!

[#2]

@dkingsbury 

Assuming Excel 2021 or 365:

in E2:

=LET(
    rws,    ROWS(MyRange),
    seqK,   SEQUENCE( rws * COLUMNS(MyRange),,0 ),
    seqR,   1 + MOD(seqK, rws),
    seqC,   1 + QUOTIENT(seqK, rws),
    stack,  INDEX(MyRange, seqR, seqC),
    FILTER(stack, stack <> "")
)

[#3]

@dkingsbury You may want to look into PowerQuery to achieve this, as demonstrated in the attached file.

Alternatively, as in Insider (beta channel) you can use this:

=TOCOL(Table1,1,TRUE)

Edit: Added another, even simpler PQ solution.

[#4]

Thanks! This works well!

[#5]

Welcome. Glad I could help
There's a link to mark solutions (can help those who search this site...)