Find data in various positions in a column and return the cell contents

[JeffGrant]

Hi All,

I am a bit stuck. All assistance is very graciously received and very much appreciated.

Here is my scenario.

I past a column of data into a sheet. No Problem. In the attached sheets, that is Column A.
The first 2 cells in the pasted column data, never change the type of data or the location. So I can extract that easy enough into Column C.
The next 4 cells in the extracted data, Column C, do change the row and can be anywhere in rows 14 to 40. With 3 of the 4 cells, the data is extracted all from the same pasted cell. This is no problem, if I now the row number that it gets pasted to.

I would like to automate the extraction for cells C10, C11 & C13. I am happy to manually type the data for C12, because there are several different types of data here.

For example, some of the data in C12 could be: Mdn HCP, hcp, (c1), (BM60), (bm48+), Class 2, just to name a few. I think there too many variants in this one to automate for now.

I have looked at the Aggregate/Index and Rows functions and I just don't seem to be able to put it together and make it work.

I hope I have pasted the mini-sheet correctly.

Test.xlsx
	A	B	C	D	E	F
5			Calculated Result	Required Result
6	Pasted Data		My Formula Attempt, using fixed cell locations
7						Comment
8	Canterbury	Track	Canterbury	Canterbury		Fixed Cell location
9	Good (4)	Condition	Good (4)	Good(4)		Fixed Cell location
10		Distance (m)	1200	1200		Can be anywhere between rows 14 and 40
11	1	Race Number	1	1		Can be anywhere between rows 14 and 40
12		Class		Mdn HCP		Can be anywhere between rows 14 and 40
13	2	Time	12:45 PM	12:45 PM		Can be anywhere between rows 14 and 40
14
15	3
16
17	4
18
19	5
20
21	6
22
23	7
24	1200m R1 Vinery Stud Mdn Hcp
25	12:45
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
C8	C8	=INDEX(A8:A28,1,1)
C9	C9	=INDEX(A8:A28,2,1)
C10	C10	=VALUE(IF(FIND("m ",A24,1)=5,LEFT(A24,4),LEFT(A24,3)))
C11	C11	=VALUE(MID(A24,SEARCH("R",A24,1)+1,2))
C13	C13	=INDEX(A8:A34,18,1)

 

[JeffGrant]

Solved this issue by changing >15 to =MAX(LEN(A1:A40)). Works a treat. Thanks again

 

[Peter_SSs]

The Spill errors (none were showing in your post by the way) were most likely because you had more than one cell in the range longer than 15 characters. Your formula below solves that** by looking for the cell with the longest text in the range.

**It would still cause an issue if there was more than one cell with equal longest text, but perhaps that will never happen?

Solved this issue by changing >15 to =MAX(LEN(A1:A40))

Hi Peter, I fixed the #spill error by using a length >20. Using a length >10 increased the spill range to 3 cells.

Where can i learn more about this?

Here is one place to start: Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News