Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Comma Counting

0

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 

=MID(V25913,FIND(",",V25913)+1,FIND(",",V25913,FIND(",",V25913)+1)-1-FIND(",",V25913))

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

Answer
Discuss

Answers

0
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.

Discuss


Answer the Question

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