In need of (likely) complex formulas to seperate 1 cell's data into multiple cells

[syncguy]

I'm trying to distribute data that exists in one cell to separate cells. The given data exists in column A and I imagine that columns B,C,D,E etc will each need formulas to grab the pertinent data (first name, middle name, last name, class, grade %) . I'm using Excel for Mac and can't use macros. Any help would be amazing

(I've attached a sample worksheet and a pic)

TEST TEMPLATE.xls
Screen Shot 2014-04-14 at 3.22.19 PM.png

[mikeTRON]

Why not use text to columns?

[syncguy]

Thanks MikeTron . What is that ?

Why not use text to columns?

[FDibbins]

Dont think that will work, it looks like you dont always have a middle name?

I am working on something, but its taking some time

[syncguy]

Thanks FDibbins ,
yes that's true. I figured that would be an odd issue

Dont think that will work, it looks like you dont always have a middle name?

I am working on something, but its taking some time

[FDibbins]

I have the 1st student sorted out.

Is this a 1-off thing, or will you need to do it repeatedly? And I dont mean do you need to do it for 500 lines, I mean, will you need to do it for 500 lines 4 times a month?

Also, where is this data coming from, and how?

[FDibbins]

OK I ended up using helper columns to break out the 3 students (otherwise the formula would become a nightmare!!!)

The part I started with, is above, I only went as far as the 1st student.

I suggest using the method from row 19, down. It can of course, be copied up and down as far as you need.. I messed around with your test data and added/removed a middle name, it looks like it is working fine - test please.

[syncguy]

genius! This is totally working perfectly. Thanks so much !! I'll experiment with it further and report back

OK I ended up using helper columns to break out the 3 students (otherwise the formula would become a nightmare!!!)

The part I started with, is above, I only went as far as the 1st student.

I suggest using the method from row 19, down. It can of course, be copied up and down as far as you need.. I messed around with your test data and added/removed a middle name, it looks like it is working fine - test please.

[FDibbins]

Happy to help andthanks for the feedback

Like I asked previously though, where is this data coming from? Maybe the import can be done differently?