Pulling numbers from a multiple alphanumeric string in a single cell.

[waruta]

I've been looking and looking, but I can't get any of the Google search formulas to work on my alphanumeric string. I just completed a survey and the first 2 questions were multiple choice from 40 options. Users were asked to pick 10 options for each of the questions.

I'm now left with a single answer cell that contains anywhere from 5 - 10 alphanumeric answers in it, and I would like to separate those answers without the text into single columns. The picture below may help explain a little more:

I would need to sort the above answers into the columns below (the order, ascending or descending doesn't matter)

I only need the numerals in the columns, the text is not important. I would preferably like to use a formula instead of VB or a Macro since this data will all be tied into other sheets and charts and whatnot.

Thank you very much for your time.

Regards,

Wouter

 

[Yahya]

Try this array formula ( arrary formula Entered with Ctrl+Shift+Enter)
in cell E3

=MID(C3,MATCH(1,IF(CODE(MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))<=57,1),0),SUM(0+ISNUMBER(0+MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))))

 

[waruta]

Try this array formula ( arrary formula Entered with Ctrl+Shift+Enter)
in cell E3

=MID(C3,MATCH(1,IF(CODE(MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))<=57,1),0),SUM(0+ISNUMBER(0+MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))))

Thanks for the code, but this didn't help, it just gave a 0 when there is definitely a 1 in the first cell. It may help to know that the answers are all on sheet 1 while this code will all be on sheet 2...