Looking for a formula (non-VBA method) to extract only non-blank values to a new column

[d0rian]

See attached image. I frequently have columns of data like col A (of 10,000 rows or more), and I want to generate the output shown in col B. (i.e. shift all of the actual, non-blank / non-null values to the top). I currently resort to a helper column like I show in column E, which evaluates the length of the input cells, and then I would use a simple INDEX/MATCH function in col F.

What I want, however, is a single formula I could paste in cells B2-B17 that would generate the output shown. I want to do this with a FORMULA, not any VBA code, but I'm stumped...

EDIT: Am hoping to find a non-array-formula-based solution to this (since for some reason arrays seem to bloat / crash my already large/volatile file...I found some pages that purport to have array-based suggestions, but hoping this is possible without...)

**EXCEL 2007

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

For xl2007 I think you will have to stick with your helper column approach, or use a CSE formula. If you had a slightly newer version it would be possible.