Comma Counting


Hello there,

I'm trying to write a formula that can read any information between the 3rd comma and 4th, I got the first part where the formula will read anything between the first and second comma 


How can you modify it to do the same but for the 3rd comma?



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.


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.


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.


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.


Answer the Question

You must create an account to use the forum. Create an Account or Login