Selected Answer
This is the formula your lecturer seems to require.
=MID($C2,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-3)),LEN($C2))-IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)-1)
In my opinion it isn't the one to be used, however, because it returns strings starting with a blank space. The desired result still includes the leading blanks. However, I would eliminate them by embedding the above in a TRIM() function. The resulting formula would be the following.
=TRIM(MID($C2,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1,IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-3)),LEN($C2))-IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)-1))
The formula is designed to be identical for all cells in all columns. It is also designed to be copied into cell D2 and copied from there to other cells in your table.
Note that column D is the fourth column. The function COLUMN() returns the number of the column in which it is found. When placed in column D it returns 4. COLUMN()-3 returns a different number, depending upon which column the function is in. When in column D, Column()-3 returns 1. In column E it returns 2 etc. COLUMN()-4, when found in column D returns 0, in column C it would be -1.
Now, the MID() function has this syntax:-
=(MID([Text], [Start], [Number of characters])
- In the above formula [Text] = $C2
The column must be absolute because the formula will be copied to the right and the Text never changes.
- [Start] is the number of the first character to be included in the mid-string:
= IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)+1
- [Number of characters] = IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-3)),LEN($C2))-IFERROR(FIND(CHAR(160),SUBSTITUTE($C2,",",CHAR(160),COLUMN()-4)),0)-1
The SUBSTITUTE() function has the following syntax.
= SUBSTITUTE([Text], [Old Text], [New Text], [Instance])
You can substitute the nth instance of a comma with another character. The formula uses the counter COLUMN()-3 to determine the instance (1st comma in column D, second comma in column E etc). The formula replaces the comma with CHAR(160), which isn't normally found in text, and then lets the FIND() function find its position in the string.
The SUBSTITUTE() function will return an error if the [Instance] is zero or it is greater than the number of available instances in the original text. Therefore each FIND(SUBSTITUTE()) function is embedded in an IFERROR() function.
The IFERROR() function has the following syntax= IFERROR([Function], [Alt Function or Result]). In the above formula, if the SUBSTITUTE() function fails (because the instance wasn't found) the FIND() function will also fail, resulting in an error which triggers the alternative. The alternative result or function is different for the various uses.
Your lecturer was well aware that you might try to get a solution from the Internet. Therefore he/she asked for explanations. I have provided the function and explained how it works. Don't think for a moment that you will be home scot-free with a simple copy/paste. Your part of the deal is to study what I have explained and fully understand what the function does and how it does it. What I am giving you is just a pointer. You will still have to invest an hour for study. If you need clarification please don't hesitate to ask.