Extract Data from Row

[Celticshadow]

Hi Forum

I receive a row of data each day which is a list of horse racing tipsters and their current record and todays tip along with other associated information. The row of data starts with tipster Nos 1 and each tipsters row of information ends at the word more this pattern follows through from Nos 1 to Nos 54 in sheet A.

What I would like is a formula that I could place in cell A2 (sheet B) and copy it down the page to extract the data between each numbered tipster and the word more so that I would have a daily list of all the tipsters through to tipster Nos 54. I have attached a google doc that I hope will give a clearer picture of how the data arrives (sheet A) and how I want it presented (sheet B), many thanks for reaading and I look forward to a solution for my query.

Tipster

A 1,The Punt,(Racing Post),Mayhem Mya silk,Mayhem Mya Exeter - 13:55,19,35,46.1 %,P&L: £ 24.89,11/10,Win strike rate: 35.2%,Current winning streak: 0,Biggest priced odds: 200/1,Total naps: 54,Current losing streak: 3,Median odds: 10/1,More,2

docs.google.com

PS The data in sheet B is just showing the first 14 tipsters for information, obviously I would like the full 54 to eventually be shown.

 

[Fluff]

Is this for Excel or for Google Sheets?

 

[Celticshadow]

Hi Fluff

Many thanks for replying it is much appreciated. The query and solution is for excel and I was just using google docs to show an example, hope that is suffice. Once again many thanks for replying.

Regards

 

[Fluff]

Ok, how about

=LET(d,A!A1:AZZ1,c,HSTACK(0,FILTER(COLUMN(d),d="more")),s,SEQUENCE(COLUMNS(c)-1),IFNA(DROP(REDUCE("",s,LAMBDA(x,y,VSTACK(x,CHOOSECOLS(d,SEQUENCE(INDEX(c,y+1)-INDEX(c,y),,INDEX(c,y)+1))))),1),""))

 

[Celticshadow]

Hi Fluff

Your above formula works superbly well and I am extremely grateful for your time and expertise lent, it is very much appreciated. May I also wish you a very happy xmas and a peaceful, healthy and prosperous new year, that also goes for all members of this fantastic forum.

Kind Regards

 

[Fluff]

You're welcome & thanks for the feedback.

Also a happy Xmas & new year to you as well.