Returning specific texts within a wide range of data coming from the Web

E

enelyam25

New Member
Joined
Aug 5, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am just new here on this forum. Hope everyone's doing well.

I hope someone from this forum will help me on my data. I am doing an excel file wherein the data that I get from a website then paste it on the DATA sheet using CTRL + ALT + V then Text.
What I need is to extract the texts that was written in CAPITAL LETTER (those rows that are highlighted in YELLOW). And the result will be on C2:C10

Please note that the data will not always be the same but will have the same pattern which I needed for the result.
Kindly see sample data. The example uploaded in Mini-sheet is not all the data since it has limit only, as you can see in the result it shows more.

Thank you in advance and will highly appreciated those who will view and response my post.

Regards.

spotlights.xlsx
ABCD
1RESULT
21AURORA VEGA
32SPY
43IMPAKT ST GEORGES
54LISPENDENSE
65THE STRIKER DYLAN
76SAINT SAM
87KING FERDINAND
98KING PEAK
109
1110
12
13PASTE>>
14Digital Newspaper
15Horse Tracker
16Free Bets
17My Bookmakers
18Subscribe
19Log in
20Search horses, jockeys and trainers …
21
22Next race off
23Racecards
24Results
25News
26Racing Tips
27Raceday Live
28Bloodstock
29Sport Tips
30Greyhounds
31Shop
32Statistics
33Pools
34More
35Next race 116 MINS
3614:05 Cork
37FREE BETS
38Check out all our Offers on our Free Bets pages
39Log In
40My Bookmakers
41Free Bets
42Home
43Racecards
44Results
45News
46Racing Tips
47Raceday Live
48Bloodstock
49Sport Tips
50Greyhounds
51Shop
52Statistics
53Pools
54Ten To Follow
55How To Bet
56Guide To Racing
57Safer Gambling
58Place Bets
59Newspaper
60Subscribe
61Horse Tracker
62Help
63Royal Ascot
64Grand National
65Cheltenham Festival
66Today
67Tomorrow
68Wed 7
69Thu 8
70Fri 9
71Sat 10
72Sun 11
73Big Race Entries
74US Racing
75Cork (IRE) 2:05 2:35 3:05 3:35 4:05 4:35 5:05 5:35
76Choose Race
77Cork
785-Aug-24
79 GOOD TO YIELDING (Showers)
80Standard
81At-a-Glance
822:05
83Mallow Mares Maiden Hurdle (4yo+) Winner €7,375 15 runners 2m1f Good To Yielding RTV
84race conditions €12,500 guaranteed For 4yo+ Weights 4yo 11st 4lbs; 5yo+ 11st 7lbs Penalty value 1st €7,375.00 2nd €2,375.00 3rd €1,125.00 4th €500.00 5th €250.00 6th €125.00
85NO.
86FORM
87HORSE
88AGE
89WGT
90OR
91JOCKEY
92TRAINER
93TS
94RPR
95ODDS
961
97Runner Jacket
98Aultown Keppols
9918
100P/9
1016
10211
1037
104-
105J:
106Sam Ewing
107T:
1082
109Peter Fahey
11033
11152
11259
1132
114Runner Jacket
115Aurora Vega
116D bf 2 tips
11796
11811143-
1196
12011
1217
122-
123J:
124Paul Townend
125T:
126W P Mullins
12741
128-
129-
1303
131Runner Jacket
132Ballinvir Rose
1338
13411
1357
136-
137J:
138Alan King
1397
140T:
141Ms M Flynn
14267
143-
144-
1454
146Runner Jacket
147Cathryns Ruby
148(113P)
1494-
1507
15111
1527
153-
154J:
155Liam Quinlan
1563
157T:
1581
159Ray Hackett
16050
161-
162-
1635
164Runner Jacket
165Coppola
16666
1670P
1685
16911
1707
171-
172J:
173J J Slevin
174T:
175P M Cloke
1769
17758
1786
179Runner Jacket
180Dontcall
18124
1826PU
1836
18411
1857
186-
187J:
188Simon Torrens
189T:
190P J Rothwell
19139
192-
193-
194NR
195Runner Jacket
196Firm Belief
197(11F)
1986
19911
2007
201-
202J:
203NON-RUNNER
204T:
205Rodger Sweeney
206-
207-
2088
209Runner Jacket
210Folly Beach
2111 tip
21217
21324
2146
215h
21611
2177
218-
219J:
220Liam McKenna
2215
222T:
223M A Molloy
22485
225120
2269
227Runner Jacket
228Happy Friend
229406
2304-
2316
23211
2337
234-
235J:
236Phillip Enright
237T:
2381
239T J O'Mara
240-
241-
24210
243Runner Jacket
244Hero In The Sky
245381 (91P)
246042-F
2475
248t
24911
2507
251-
252J:
253Mr L Burke-Ott
2547
255T:
2562
257Alexander Ott
258-
259-
26011
261Runner Jacket
262Izzy Show Bizzy
26318
2649
2656
26611
2677
268-
269J:
270Michael O'Sullivan
271T:
272Eugene M O'Sullivan
273100
274-
27556
276NR
277Runner Jacket
278Jana Colombe
27955
2805
2815
28211
2837
284-
285J:
286Jack Kennedy
287T:
288Gordon Elliott
289-
290-
29113
292Runner Jacket
293Lady Nightingale
29465
2954P0-7
2965
29711
2987
299-
300J:
301Brian Hayes
302T:
3032
304Paul Stephen Kiely
305-
306-
30714
308Runner Jacket
309Mount Shenshan
31031
311683-76
3126
313h
31411
3157
316-
317J:
318Jack Foley
319T:
320Thomas Mullins
32130
32250
32391
32415
325Runner Jacket
326Mrs Wemyss
32766
3285P
3296
33011
3317
332-
333J:
334Shane Fenelon
3357
336T:
337E Sheehy
33820
339-
340-
34116
342Runner Jacket
343Who Ate The Jam
34413
3450
3466
34711
3487
349-
350J:
351Trevor Ryan
3525
353T:
354John F Gleeson
355-
35616
35717
358Runner Jacket
359Keep It Cool
36028
3617
3624
36311
3644
365-
366J:
367Paddy Hanlon
3687
369T:
370John Joseph Hanlon
37133
372-
373-
374R18
375Runner Jacket
376Inch Alainn
37732
378P7P6-0
3795
38011
3817
382-
383J:
384Ricky Doyle
385T:
3862
387W J Burke
38850
38949
39084
391R19
392Runner Jacket
393Opera Point
39431
3956B0/00
3966
39711
3987
399-
400J:
401Mr J Aherne
4027
403T:
404A M O'Grady
40537
40682
407NR
408Runner Jacket
409Whiskeyforluck
41028
411U60
4125
413p
41411
4157
416-
417J:
418T:
419Eric Larkin
420-
421-
422Betting Forecast 4/6 Aurora Vega, 5/2 Folly Beach, 8/1 Jana Colombe, 16/1 Mount Shenshan, 25/1 Cathryns Ruby, 33/1 Ballinvir Rose, Happy Friend, Keep It Cool, Lady Nightingale, 50/1 Aultown Keppols, Mrs Wemyss, 66/1 Hero In The Sky, 100/1 Coppola, Dontcall, Izzy Show Bizzy, 150/1 Who Ate The Jam.
423 Willie Mullins' AURORA VEGA has smart form in bumpers and this well-bred mare will be hard to beat if taking to this discipline.
4242:35
425Fermoy Handicap Hurdle (4yo+) Winner €8,850 11 runners 2m1f Good To Yielding RTV
426race conditions €15,000 guaranteed For 4yo+ (123 = 11st 12lbs) Minimum weights: 10-0; Penalties (after calculation of the handicap), each hurdle won 7lb Dark Note's Handicap Mark 122 Penalty value 1st €8,850.00 2nd €2,850.00 3rd €1,350.00 4th €600.00 5th €300.00 6th €150.00
427NO.
428FORM
429HORSE
430AGE
431WGT
432OR
433JOCKEY
434TRAINER
435TS
436RPR
437ODDS
438NR
439Runner Jacket
440Dark Note
441D
4423
44328-2P9
4445
44511
44612
447122
448J:
449Mr A P Ryan
4507
451T:
452Andrew Slattery
453106
454127
455NR
456Runner Jacket
457Grange Walk
458D
4591
4603640-5
4619
46211
4636
464116
465J:
466Shane Fitzgerald
467T:
468John Patrick Ryan
46995
470121
4713
472Runner Jacket
473Disco Dancer
474CD bf
47521
476-42312
4775
478t
47911
4804
481114
482J:
483Jack Kennedy
484T:
485Gordon Elliott
48649
48797
488124
4894
490Runner Jacket
491Major Barry
4922 tips
49318
494Jul-13
4956
49611
4974
498114
499J:
500Danny Gilligan
5013
502T:
503John E & Thomas Kiely
504116
505126
5065
507Runner Jacket
508Hey Johnny
509D
510162 (14F)
511/6P0P-
5127
51311
5142
515112
516J:
517Shaun Greene
5187
519T:
520Thomas Mullins
52130
52290
523119
5246
525Runner Jacket
526Mary's Pride
52732
5280PP2-1
5297
53011
5311
532111
533J:
534Jordan Gainford
535T:
536Edward Cawley
537100
538113
539127
5407
541Runner Jacket
542The Niffler
5431 tip
54432
54512-183
5467
547ht
54811
5490
550110
551J:
552Sam Ewing
553T:
554Eamonn O'Connell
55595
556129
5578
558Runner Jacket
559Flidais
560D
56113
562Feb-51
5636
56410
56513
566109
567J:
568Danny Mullins
569T:
570John Patrick Ryan
57129
572101
573124
5749
575Runner Jacket
576Spy
5771 tip
57818
579443P-6
5805
58110
58213
583109
584J:
585Philip Byrnes
586T:
587C Byrnes
58825
58985
590127
59110
592Runner Jacket
593Marvel Fan
594D
59534
596640-3F
5978
598t
59910
60012
601108
602J:
603Daniel King
604T:
605Mrs C O'Leary
60611
607-
608126
60911
610Runner Jacket
611Only A Dollar
612D
61350
614478-17
6156
61610
6179
618105
619J:
620Phillip Enright
621T:
622Sean Aherne
623100
624127
62512
626Runner Jacket
627Makfils
6281 tip
62932
630209-18
6317
632t
63310
6344
635100
636J:
637Jake Coen
6383
639T:
640Peter Fahey
64133
642114
643126
64413
645Runner Jacket
646Only One Plan
647D
64813
64947-023
6507
651p
65210
6532
65498
655J:
656Conor Owens
6577
658T:
659P J Rothwell
66039
661106
662125
663Betting Forecast 6/1 Major Barry, 13/2 Disco Dancer, Flidais, Mary's Pride, 15/2 Spy, 8/1 Only One Plan, The Niffler, 10/1 Grange Walk, Marvel Fan, 12/1 Dark Note, Makfils, 16/1 Only A Dollar, 18/1 Hey Johnny.
664 One suspects that SPY, who came home well when sixth at Killarney last month, has a handicap or two in him in this discipline
6653:05
666Adare Manor Opportunity Maiden Hurdle (4yo+) Winner €8,555 15 runners 2m2f100y Good To Yielding RTV
667race conditions €14,500 guaranteed For 4yo+ Weights 4yo 11st 9lbs; 5yo+ 11st 12lbs Allowances fillies & mares 7lbs; (which are not cumulative, up to and including 1st August), riders who have not ridden 25 winners 4lb; who have not ridden 40 winners 2lb Penalty value 1st €8,555.00 2nd €2,755.00 3rd €1,305.00 4th €580.00 5th €290.00 6th €145.00
Sheet1
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Welcome to the MrExcel board!

enelyam25 said:
What I need is to extract the texts that was written in CAPITAL LETTER
Click to expand...
Looks like only parts of the cells you want are in capital letters. But, for example, cells B35 and B37 (& lots of others) contain some capital letters too. What is the logic for which ones you want and which ones you don't want?
 
Last edited:
Upvote 1
The way it looks to me is that the data is from https://www.racingpost.com/racecards/596/cork/2024-08-05 and that the text required is from the Cell After the cell that starts with "Betting Forecast"
(the word Verdict seems to have disappeared)
@enelyam25 does that sound right to you ?

1722862630012.png


20240805 VBA Extract Capital Letter words betting enelyam25.xlsm
AB
422Betting Forecast 4/6 Aurora Vega, 5/2 Folly Beach, 8/1 Jana Colombe, 16/1 Mount Shenshan, 25/1 Cathryns Ruby, 33/1 Ballinvir Rose, Happy Friend, Keep It Cool, Lady Nightingale, 50/1 Aultown Keppols, Mrs Wemyss, 66/1 Hero In The Sky, 100/1 Coppola, Dontcall, Izzy Show Bizzy, 150/1 Who Ate The Jam.
423 Willie Mullins' AURORA VEGA has smart form in bumpers and this well-bred mare will be hard to beat if taking to this discipline.
Sheet1
 
Upvote 1
Alex Blakenburg said:
The way it looks to me is that the data is from Horse Racing Results | 5 August 2024 | Time Order | Racing Post and that the text required is from the Cell After the cell that starts with "Betting Forecast"
(the word Verdict seems to have disappeared)
@enelyam25 does that sound right to you ?

View attachment 114958

20240805 VBA Extract Capital Letter words betting enelyam25.xlsm
AB
422Betting Forecast 4/6 Aurora Vega, 5/2 Folly Beach, 8/1 Jana Colombe, 16/1 Mount Shenshan, 25/1 Cathryns Ruby, 33/1 Ballinvir Rose, Happy Friend, Keep It Cool, Lady Nightingale, 50/1 Aultown Keppols, Mrs Wemyss, 66/1 Hero In The Sky, 100/1 Coppola, Dontcall, Izzy Show Bizzy, 150/1 Who Ate The Jam.
423 Willie Mullins' AURORA VEGA has smart form in bumpers and this well-bred mare will be hard to beat if taking to this discipline.
Sheet1
Click to expand...
Hi,

Yes. Think that's the clue on that. So after the Betting Forecast, I want to get the one in the Verdict section that's in Capital letter.
 
Upvote 0
enelyam25 said:
So after the Betting Forecast, I want to get the one in the Verdict section that's in Capital letter.
Click to expand...
I am unsure what the "Verdict section" is or how it is identified but you could give this code a try with a copy of your data. It looks in the cells immediately below the "Betting Forecast ..." cells. It may need some tweaking depending on what punctuation may immediately follow the upper case letters and/or what punctuation (if any) might occur within the upper case letters.

VBA Code: 
Sub Extract_Names()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^| )([A-Z ']{3,})(?= |\,|$)"
  a = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    If Left(a(i, 1), 16) = "Betting Forecast" Then
      If RX.test(a(i + 1, 1)) Then
        k = k + 1
        b(k, 1) = RX.Execute(a(i + 1, 1))(0)
      End If
    End If
  Next i
  If k > 0 Then Range("C2").Resize(k).Value = b
End Sub
 
Last edited:
Upvote 1
Solution
Or
Code: 
Sub test()
    Dim a, e, x, y, i&, ii&, n&
    x = Filter([transpose(if(left(b14:b10000,16)="Betting Forecast",b15:b10001))], False, 0)
    ReDim a(1 To 10000, 1 To 1)
    For Each e In x
        y = Split(Replace(e, ",", ""))
        For i = 0 To UBound(y)
            If (Len(y(i)) > 2) * (Not y(i) Like "*[a-z0-9]*") Then
                n = n + 1: a(n, 1) = y(i): ii = 1
                Do While (y(i + ii) <> "") * (Not y(i + ii) Like "*[a-z0-9]*")
                    a(n, 1) = a(n, 1) & " " & y(i + ii): ii = ii + 1
                Loop
                i = i + ii - 1
            End If
        Next
    Next
    If n Then [c2].Resize(n) = a
End Sub
 
Upvote 1
Peter_SSs said:
I am unsure what the "Verdict section" is or how it is identified but you could give this code a try with a copy of your data. It looks in the cells immediately below the "Betting Forecast ..." cells. It may need some tweaking depending on what punctuation may immediately follow the upper case letters and/or what punctuation (if any) might occur within the upper case letters.

VBA Code: 
Sub Extract_Names()
  Dim RX As Object
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^| )([A-Z ']{3,})(?= |\,|$)"
  a = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a) - 1
    If Left(a(i, 1), 16) = "Betting Forecast" Then
      If RX.test(a(i + 1, 1)) Then
        k = k + 1
        b(k, 1) = RX.Execute(a(i + 1, 1))(0)
      End If
    End If
  Next i
  If k > 0 Then Range("C2").Resize(k).Value = b
End Sub
Click to expand...
Hello Peter,

This one works. Tried to paste new data and its working as it is.

Thank you so much for your help. Very much appreciated!! Hope you continue helping more members here.

Cheers!
 
Upvote 0
Fuji said:
Or
Code: 
Sub test()
    Dim a, e, x, y, i&, ii&, n&
    x = Filter([transpose(if(left(b14:b10000,16)="Betting Forecast",b15:b10001))], False, 0)
    ReDim a(1 To 10000, 1 To 1)
    For Each e In x
        y = Split(Replace(e, ",", ""))
        For i = 0 To UBound(y)
            If (Len(y(i)) > 2) * (Not y(i) Like "*[a-z0-9]*") Then
                n = n + 1: a(n, 1) = y(i): ii = 1
                Do While (y(i + ii) <> "") * (Not y(i + ii) Like "*[a-z0-9]*")
                    a(n, 1) = a(n, 1) & " " & y(i + ii): ii = ii + 1
                Loop
                i = i + ii - 1
            End If
        Next
    Next
    If n Then [c2].Resize(n) = a
End Sub
Click to expand...
Hello Fuji,

I very much appreciated your reply/help on this thread. It did run as well as I expected too.
Thank you so much for giving your time to help on my data. Hope you continue to help more members here as well

Cheers!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top