Extract until next criteria

Hello Excel friends,

A file is attached with examples in the second sheet. There are two columns in sheet 1 which need sorted into four columns. I know how to do this when the number of cells between each criteria is equal, but that's not the case here.

A cell will contain six characters, beginning with a number, then names below that, and I would need to extract all of the names corresponding to each size character cell. 1DWAGA might have Smith and Albert under it, and those need to be in two rows rather than all in one column.

If I could create some index or vlookup which extracts all information up to the next cell that begins with a number, that would be one thing, but I'm stuck here.

Any help on this puzzle is appreciated!

Hi

Try this with helper columns in C5, D5 and E5 respectively, and drag down

Formula:

Please Login or Register  to view this content.

The table must be ended with a value in column B (in the sample I set B1000 with 0)

See the file for clarification

Hi

I add some adjusts

Formulas are

Formula:

Please Login or Register  to view this content.

and the file

Hmm, that's very interesting, but there's no first name field, and even if I got rid of the extra info in the cabin and booking fields, there's lots of things in the last name field that shouldn't be there either.

I'm not sure there's a clever way to do this, since some cabins contain people with two different last names. I feel like the unique records might need filtered to a separate column first, and then separated from there, but the names would be the issue at that point..

Anything with a Mr or Mrs next to it is a first name, anything without is a last name, and anything with a 4 digit pattern next to it is a booking number/cabin number. Hmm.