Extract seven pieces of data .

**I need excel help** · 05-17-2019, 09:39 PM

Hello all ,

I am hoping to get functions or a macro that can give me the 7 x pieces of data I have in cols BK2:BO2

The main problem I have is col BJ always varies in field size .

There could be anywhere from 4 horses in a race up to 14 . This is very random .

The seven results are all taken from below the words Dividends and Times .

The mile rate I have changed to format of time so I can sort from lowest to highest times .

The trick I believe is to obtain the results from below the two words mentioned above or at least getting the whole
line , example BJ7 and BJ11 then splitting these .

Any ideas would be great .

Thanks .

**huuthang_bd** · 05-17-2019, 10:37 PM

You can try bellow array formulas (finish with Ctrl + Shift + Enter):

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

If your data is large, maybe you need a macro.

**jindon** · 05-17-2019, 10:46 PM

try

Please Login or Register  to view this content.

**I need excel help** · 05-17-2019, 11:06 PM

Humble apologies to you both .
I should of said i need results to go with the 1 , 2 , and 3 placed horses only . Basically the second race result would start in cell BK12 .

Jindon your macro is perfect apart from my major blunder of my above opening line .

Huuthang_bd your functions are great but col BM is giving me all result of 0.0014 and also my blunder .

Really happy at this point . Sorry again for my mistake .

**jindon** · 05-17-2019, 11:15 PM

If you want only to 3rd, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**huuthang_bd** · 05-18-2019, 12:18 AM

Originally Posted by I need excel help

Huuthang_bd your functions are great but col BM is giving me all result of 0.0014 and also my blunder .

Are you format column BM as time type (mm:ss.0)?

Originally Posted by I need excel help

I should of said i need results to go with the 1 , 2 , and 3 placed horses only . Basically the second race result would start in cell BK12 .

Retry with below normal formulas

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Don't forget format column BM as time type.

**I need excel help** · 05-18-2019, 01:41 AM

Thanks huuthang_bd your second lot of functions are perfect . And yes i didn't format column to time , all good .

Jindon i have included another version 2 which is what results should be .

Thanks .

**jindon** · 05-18-2019, 02:04 AM

Change to

Please Login or Register  to view this content.

**I need excel help** · 05-18-2019, 03:02 AM

Thanks jindon , nearly there your latest macro is putting data in correct cells .

All that's missing is the 3rd dividend which i have highlighted in yellow in the version 3 sheet enclosed .

If possible are you able to change col BM results to match my col BS , as i need it in this type format for sorting later, example 01:59.5
02:02.4
01:59.8
01:58.8

Not 1-59.5 , 2-02.4 , 1-59.8 , 1-58.8

I feel i will adding some more reputation soon .

Thanks .

**jindon** · 05-18-2019, 03:04 AM

Then just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**I need excel help** · 05-18-2019, 07:13 PM

Thanks jindon your macro now works perfectly and i had a function to repair mile rate time .

Thanks also to huuthang_bd for your functions which i can also use .

Very happy thank you both .

**I need excel help** · 05-19-2019, 10:33 PM

Jindon , i went to test the macro on my larger sheet of 12734 rows and got this line of code in yellow .

txt = Join(Filter([transpose(if(bj2:bj1000<>"",bj2:bj1000))], False, 0), vbLf)

Run time error 13 type mismatch .

Really only guessing here do i maybe have to extend the 1000 in this code .
Thanks .

**jindon** · 05-19-2019, 10:44 PM

2 possibilities.
1) Error value within the cell
2) any one cell has characters more than 255 characters.

Othereise it should work changing by

Please Login or Register  to view this content.

**I need excel help** · 05-20-2019, 01:48 AM

Thanks for that jindon .
Your right i had 30 x cells >=255 characters . Now fixed .
Found 6 x empty cells too .

Also getting issue when two horses dead heat for place 1 , must check 2 and 3 also .

I changed the deadheats for 1 and 2 and macro all started working . I can fix those couple later .

I will give this some more time tomorrow and i think im one step away to perfection again . Learning .

Thank you .

Extract seven pieces of data .

LinkBack

Thread Tools

Rate This Thread

Display

Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Re: Extract seven pieces of data .

Thread Information

Users Browsing this Thread

Similar Threads

(SOLVED) Pieces of data in query extract is not recognized as numbers

[SOLVED] Extract two pieces of data from column A

Is it possible to extract particular pieces of data from a list of URLs in Excel 2010?

extract multiple pieces of text between two strings

Looking up several pieces of data

Multiple Data Pieces

Average of 3 pieces of data from within a row

Bookmarks

Bookmarks

Posting Permissions