Required formula to extract every occurrence of a sting from a column.

Hi All,

I have a spreadsheet that pastes a lot of information for a singular horse race in one column.
There can be up to 24 horses in a race, which means the column of data can easily reach 1500 cells.
When the data is pasted, the horse name is immediately followed by the allocated weight in the cell underneath the name cell in every instance.
However, where this data is pasted to does vary with every race. So I can not simply hard code a cell reference.
I need to extract the horse name and allocated weight into an adjacent summary column.

I could do this via VBA by testing every cell and then writing out the results, but I would prefer to be able to do it as formula(s) on the spreadsheet itself.

Please see the attached sheet for sample data.

I sincerely appreciate any assistance.

Thanks in advance.

I am not immediately seeing rhyme or reason behind the pasted data.

though it looks like what you want to do is possible at first glance.

Questions
1) Is the horse name ALWAYS followed by a "Number" + decimal (eg: 1. , 2. , 3. , etc) (this can extract the horse name if no other cells have a number + decimal at the start of the cell.

2) Is the weight ALWAYS formatted right below the Name of the Horse? and is it always formatted in W: xx.xx"kgF" (this can extract the weight.).

Several helper rows can get these extracted then a simple if statement / rank based on number/row would get the format of the "Required Extracted Data"

In E4

=IFERROR(INDEX($A$2:$A$1000,AGGREGATE(15,6,1/(LEFT($A$2:$A$1000,3)="W: ")*ROW($A$1:$A$1000)-1,ROWS($1:1))),"")

in F4

=IFERROR(INDEX(MID($A$2:$A$1000,4,4),AGGREGATE(15,6,1/(LEFT($A$2:$A$1000,3)="W: ")*ROW($A$1:$A$1000),ROWS($1:1))),"")

Another option

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Late to the show.

Assuming my questions were yes.

I used helper columns and keeps the format to what you requested in a single column.

At any rate.

You have many different ways to get what you.

Hi all,

wow, what a great response. Thank you all for being so fast.

I am excited to try these solutions tomorrow. Will let you know how it all goes.

But for now, and pre-testing the results, I will mark this thread as solved

Regards.

Jeff