Selected Answer

Please set up the following test sheet.

```
[A10 =] A,B,C,D,E,F
[B10 =] 2
[C10 =] MID(A10,2+1,(4-2-1))
[C11 =] =MID(A10,2+1,(4-2-1))
```

Now, as you see, the formula in C11, written in plain text in C10, extracts the letter B between the first and second comma. That is the 2nd comma-separated text string, and that is the significance of the "2" in B10. We will create a formula which makes B10 variable. You will be able to insert any number which is bigger than 1 and smaller than the number of CSVs in A10. It can't extract the last value because it isn't followed by a comma.

In the formula in C10 there are two numbers you need to make variable, 2 and 4. Here is how you create the 2.

```
[D10 =] =SUBSTITUTE(A10,",",CHAR(160),B10-1)
[E10 =] =FIND(CHAR(160),D10)
```

The formula in D10 just replaces the first comma of A10 with a CHAR(160). This is a character which wouldn't normally occur in your text. In fact, Excel doesn't recognise it. Therefore it is shown as a question mark in the result of the formula. Now, as you modify the value in B10, the question mark shifts in the result string.

While B10 = 2, E10 shows 2. Please don't change B10 as yet so as not to cause confusion. Instead, observe that E10 refers to D10. To create a proper building block for your formula replace the reference to D10 with the formula in D10. I made a copy of E10 in E11. After making the replacement I had the formula below which returns 2 (while B10 = 2).

`[E11 =] FIND(CHAR(160),SUBSTITUTE(A10,",",CHAR(160),B10-1))`

Now we use the same method to create the 4 in E12. Note that the SUBSTITUTE function uses the value of B10 instead of B10-1.

`[E12 =] =FIND(CHAR(160),SUBSTITUTE(A10,",",CHAR(160),B10))`

Now you can paste a copy of C11 in D13 and replace the occurrances of "2" and "4" with the formulas in E11 and E12. Of course, you omit the leading = signs. Your formula will be easier to read, if you place each of the pasted formulas between parentheses. You arrive at this formula.

`=MID(A10,(FIND(CHAR(160),SUBSTITUTE(A10,",",CHAR(160),B10-1)))+1,((FIND(CHAR(160),SUBSTITUTE(A10,",",CHAR(160),B10)))-(FIND(CHAR(160),SUBSTITUTE(A10,",",CHAR(160),B10-1)))-1))`

Now you can backward engineer this formula to replace the variable B10 with a hard number or with a different method of providing a variable number. You might, for example, use the COLUMN() function. Enter the function below in C15 and copy to D15:F15.

`=COLUMN()-1`

The COLUMN() function, without parameter, returns the number of the column it resides in. When in C15 it is in column C which is column 3. Therefore COLUMN()-1 returns 2. As you copy the function to the right it counts.

In the variation below the reference to B10 was replaced with *COLUMN()-1*. Place the formula in C16 and copy to D16:F16.

`=MID($A10,(FIND(CHAR(160),SUBSTITUTE($A10,",",CHAR(160),COLUMN()-1-1)))+1,((FIND(CHAR(160),SUBSTITUTE($A10,",",CHAR(160),COLUMN()-1)))-(FIND(CHAR(160),SUBSTITUTE($A10,",",CHAR(160),COLUMN()-1-1)))-1))`

Observe that the reference to A10 must now be to an absolute column (*$A10*) because the fdormula is copied to other columns. Of course, *COLUMN()-1-1* may be replaced with *COLUMN()-2*. I just left the two minuses in place to show what I had done.