I have a column of data which goes something like: 0,2,4,6,8,10,8,6,4,2,0,2,4,6,8,10,8,6,4,2,0

Is there a function in excel which would just return the second range, eg: (0,2,4,6,8,10,8,6,4,2,0) and put this into a new column. I am not able to just do a simple =CELL because the location of the range within the column changes each time.

Nicola

This formula will return what you asked for, but I don't know if it will work for any other strings. Perhaps you could post some more examples along with a bit of an explanation as to what you are trying to do.

My main confusion is that what you are calling the "second range" looks exactly like the first range except that the second range ends with a 0.

In any case, this seems to work for your example:

=MID(A1,FIND(",0,",A1,2)+1,FIND(",0,",A1,FIND(",0,",A1,2))+1)

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Thanks for your reply!

What I have is data for ankle range of motion. The ankle rotates backwards and forwards and we record range of motion in degrees. So the data starts at 0 degrees and as the ankle moves it increases eg, to 1deg, 2deg, 3deg, 4deg, 5deg etc. Then once at the maximum range the ankle moves back again so goes from 5deg, 4deg, 3deg, 2deg, 1deg. The trouble is we record three identitical rotations, so... 0,1,2,3,4,5,4,3,2,1,0...0,1,2,3,4,5,4,3,2,1,0....0,1,2,3,4,5,4,3,2,1,0

But we only use data from the third rotation. So I am looking for a function that can tell me the cell reference for the data from the third rotation. I am not at all sure if this is possible! Hope this makes a little more sense...

I think you've made it even more confusing, at least to me. In your OP you used the term

second rangebut now your asking for thethird rotation. Does rotation mean range? If so, why did you change from asking for thesecondto asking for thethird?In addition, the first example you posted looked like this:

0,2,4,6,8,10,8,6,4,2,0,2,4,6,8,10,8,6,4,2,0

but your second looks like this:

0,1,2,3,4,5,4,3,2,1,0...0,1,2,3,4,5,4,3,2,1,0....0,1,2,3,4,5,4,3,2,1,0

All ranges/rotations/whatever start and end with a 0, but your original dataset did not show that. In addition, what do the "..." mean?

Finally (at least for now) I can't tell from either of your examples or from your text whether these numbers and commas are all in one cell or if each individual number is in it's own cell and the commas don't exist or what.

Keep in mind that we can't see your spreadsheet from where we're sitting, so you need to be very specific (and consistent) .

If you need to post example data in columns and rows, click on the words How To in my signature line for instructions on how to do that.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ok well I am sorry but I am new to this. I tried to keep it simple in the first post with just the two ranges, but my actual dataset has three. I guess range and rotation are the same. All the data is in one column, below is an example: Cell Data

A1 0

A2 1

A3 2

A4 3

A5 4

A6 5

A7 4

A8 3

A9 2

A10 1

A11 0

A12 1

A13 2

A14 3

A15 4

A16 5

A17 4

A18 3

A19 2

A20 1

A21 0

A22 1

A23 2

A24 3

A25 4

A26 5

A27 4

A28 3

A29 2

A30 1

A31 0What I am looking for is a function that returns the cell reference of just the last range. In the above example this would be: A21 to A31.

It's beginning to make more sense now. Another question:

In your OP you said "the location of the range within the column changes each time."

However, each of the examples you have posted show a set of 10 numbers before the second 0, then another set of 10 before the third 0. Based on your comment that "location of the range within the column changes" does that mean that there may be more or less numbers in each set?

Are we simply looking for the address of the third and fourth "zeros" in Column A?

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

The number of cells in the range will change from person to person, but not between rotations, so each rotation would have the same amount of cells. What I am looking for sounds like what you said, I need the address of the third zero to the last zero.

re: " What I am looking for sounds like what you said, I need the address of the third zero to the last zero."That's a little different than what I said. I said the third and fourth zeros, you said the third and

lastzeros. As long is the fourth zero is the same as the last zero, then we're on the same page. I'll assume that we are.Unless they've added something new to Excel 2007, I don't believe that there is any built in function to find the nth occurrence of a value in a list. Therefore, we have to do a little extra work.

To show you how this works, I'm going to shorten your list. Let's start with this and try to return A9 (the address of the third zero) and A13 (the address of the fourth zero)

A 1 0 2 1 3 2 4 1 5 0 6 1 7 2 8 1 9 0 10 1 11 2 12 1 13 0Place this formula in B1 and drag it down:

=A1 & "-" & COUNTIF($A$1:$A1,A1)

You should now have this, where the last digit of the value in Column B is the "occurrence" of the value in Column A.

A B 1 0 0-1 2 1 1-1 3 2 2-1 4 1 1-2 5 0 0-2 6 1 1-3 7 2 2-2 8 1 1-4 9 0 0-3 10 1 1-5 11 2 2-3 12 1 1-6 13 0 0-4i.e. B1 shows 0-1 which is the first occurrence of 0 in the list. B4 shows 1-2 which is the second occurrence of 1 in the list.

Note that B9 (0-3) and B13 (0-4) denote the third and fourth occurrence of 0, which are two items we are looking for.

Now, to return the

addressesof the actual zeros in column A, we'll use the MATCH function to find 0-3 and 0-4 in Column B and use the result as the ROW argument for the ADDRESS function:=ADDRESS(MATCH("0-3",B:B,0),1) will return $A$9

=ADDRESS(MATCH("0-4",B:B,0),1) will return $A$13You can place the COUNTIF function in any column you choose and even Hide that column so that it doesn't show. Drag it down as far as you think you would ever need it and it will sit there waiting for you to put data in Column A. If you use a column other than B, obviously you'll need to modify the MATCH function to reference that column.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History