# Excel function to extract string

Microsoft Microsoft office excel 2007 ac...
December 11, 2010 at 04:36:17
Specs: Windows Vista
 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,0Is 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

See More: Excel function to extract string

#1
December 11, 2010 at 06:16:58
 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 this How-To.

Report •

#2
December 11, 2010 at 06:28:12
 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,0But 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...

Report •

#3
December 11, 2010 at 10:20:01

Report •

Related Solutions

#4
December 11, 2010 at 11:10:11
 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 DataA1 0A2 1A3 2A4 3A5 4A6 5A7 4A8 3A9 2A10 1A11 0A12 1A13 2A14 3A15 4 A16 5A17 4A18 3A19 2A20 1A21 0A22 1A23 2A24 3A25 4A26 5A27 4A28 3A29 2A30 1A31 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.

Report •

#5
December 11, 2010 at 12:08:35
 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 this How-To.

Report •

#6
December 11, 2010 at 13:46:48
 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.

Report •

#7
December 11, 2010 at 15:12:09
 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 last zeros. 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 0 ```Place 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-4 ```i.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 addresses of 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 this How-To.

Report •