Removing Blank Rows in Singular Cell

I have run into a unique issue that I am sure has an obvious solution that I am either missing or not thinking of. I attempted to search online and in the forums to see if I could find a similar issue, but I could not.

Essentially, we get a list of people in one cell that are part of a group. There can be over 100-1000 groups per dataset. The issue is that for whatever reason, the data pulled will randomly have extra rows in the cell that can occur before the first person is listed, at the end, in the middle, or the data is just empty rows that excel. Please see attachment for sample examples.

I was trying to think of some way to eliminate these issues with a singular formula. Any help would be greatly appreciated.

is that what you want?

with Power Query

EXAMPLE	SAMPLE
1	Lanny Esmae
1	Spike Azaria
2
3	Hailee Devyn
4	Delice Leatrice
4	Jackalyn Bristol

Please try:

Formula:

Please Login or Register  to view this content.

This was a clever use of adding characters then using substitute. Simple yet clever.

Ditto .