EXTRACT TEXT FROM STRING TO COLUMNS

[Closed]
Report
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
Extract data from text string
Hello All
I have a spreadsheet with a number of text strings in Col A and would to extract data from them and put it in a number of other Columns as follows

The individual times are given to me in the row above each string but should be in the same row as the other data when the macro has been executed.Example 01:50 is in row 1,String is in row 2,starts with WETHERBY and ends with RUK

1.Time at the begining of the string - insert in Col E
2.If text contains "HURDLE"- Col Q insert "HDL"
If text contains "CHASE"- Col Q insert "CHS"
If text contains "NATIONAL HUNT FLAT"- Col Q insert "NHF"
If text contains none of the above - Col Q insert "FLT"
3.If text contains "Handicap" or "H'cap" Col R insert "HCP"
If text does not contain "Handicap" Col R insert "STKS"
4. If text contains "Novice"- Col BI insert "NOV"
If text contains "Maiden"- Col BI insert "MDN"
If text contains none of the above - Col BI insert "NOT"
5. Class number insert to Col O
6. Distance (found at the end of the text) insert to Col N
7. Winner amount without the comma or £ sign to Col M
(if possible rounded to 500 using the MROUND function)

Col A will then be removed and the days date inserted.
The data will be used every day so a macro would be ideal,
Many thanks for help given.

01:50
WETHERBY RACECOURSE & CONFERENCE CENTRE NOVICES' HURDLE (DIV I) (CLASS 4) (4yo+) Winner £2,599 10 runners 2m4f Good To Soft RUK 01:50 2m4f 2599 STKS 4 HDL NOV
02:20
RACHEL WRIGHT MEMORIAL HURDLE (JUVENILE MAIDEN HURDLE) (CLASS 5) (3yo) Winner £1,779 14 runners 2m110y Good To Soft RUK 02:20 2m110y 1779 STKS 5 HDL MDN
02:50
BECOME AN ANNUAL BADGE HOLDER HANDICAP CHASE (CLASS 3) (4yo+ 0-135) Winner £5,954 8 runners 2m Good To Soft RUK 02:50 2m 5954 HCP 3 CHS NOT
03:25
FOLLOW WETHERBY RACECOURSE ON TWITTER HANDICAP HURDLE (CLASS 3) (4yo+ 0-135) Winner £4,549 14 runners 2m110y Good To Soft ARL 03:25 2m110y 4549 HCP 3 HDL NOT
03:55
BOBBY RENTON HANDICAP CHASE (CLASS 3) (4yo+ 0-135) Winner £5,443 12 runners 2m4f110y Good To Soft RUK 03:55 2m4f110y 5443 HCP 3 CHS NOT
04:30
WETHERBY RACECOURSE & CONFERENCE CENTRE NOVICES' HURDLE (DIV II) (CLASS 4) (4yo+) Winner £2,599 10 runners 2m4f Good To Soft RUK 04:30 2m4f 2599 STKS 4 HDL NOV
05:00
WETHERBY RACECOURSE FOR ALL OUTDOOR EVENTS BEGINNERS' CHASE (CLASS 3) (4yo+ 0-125) Winner £4,549 9 runners 3m1f Good To Soft RUK 05:00 3m1f 4549 STKS 3 CHS NOT
05:30
2ND/3RD NOVEMBER - BET365 CHARLIE HALL MEETING HANDICAP HURDLE (CLASS 3) (4yo+ 0-140) Winner £4,791 14 runners 2m6f Good To Soft RUK 05:30 2m6f 4791 HCP 3 HDL NOT
01:40
BRITISH STALLION STUDS SUPPORTING BRITISH RACING EBF MAIDEN STAKES (CLASS 5) (2yo) Winner £3,235 13 runners 1m75y Soft RUK 01:40 1m75y 3235 STKS 5 FLT MDN
02:10
WINNING NUMBERS 01159 500500 DG CARS NURSERY (CLASS 5) (2yo 0-70) Winner £2,264 16 runners 1m75y Soft RUK 02:10 1m75y 2264 HCP 5 FLT NOT
02:40
ROSELAND BUSINESS PARK MAIDEN STAKES (CLASS 5) (3yo) Winner £2,264 15 runners 1m2f50y Soft RUK 02:40 1m2f50y 2264 STKS 5 FLT MDN
03:15
RYAN'S EVENT CLEANING SUPPORTING HORSE RACING UK HANDICAP (CLASS 3) (3yo+ 0-95) Winner £6,848 16 runners 1m2f50y Soft BSD 03:15 1m2f50y 6848 HCP 3 FLT NOT
03:45
RYAN'S EVENT CLEANING SUPPORTING HORSE RACING UK MAIDEN AUCTION STAKES (CLASS 5) (2yo) Winner £2,264 6 runners 5f13y Soft RUK 03:45 5f13y 2264 STKS 5 FLT MDN
04:20
FOR YOUR COMFORT DG CARS 01159 500500 HANDICAP (CLASS 4) (3yo 0-85) Winner £4,075 14 runners 5f13y Soft RUK 04:20 5f13y 4075 HCP 4 FLT NOT
04:50
BOOK YOUR BLACK AND WHITE CHRISTMAS PARTY HANDICAP (DIV I) (CLASS 6) (3yo+ 0-65) Winner £1,941 13 runners 5f13y Soft RUK 04:50 5f13y 1941 HCP 6 FLT NOT
05:20
BOOK YOUR BLACK AND WHITE CHRISTMAS PARTY HANDICAP (DIV II) (CLASS 6) (3yo+ 0-65) Winner £1,941 13 runners 5f13y Soft ATRL 05:20 5f13y 1941 HCP 6 FLT NOT
02:00
TAG WORLDWIDE NURSERY (CLASS 4) (2yo 0-85) Winner £3,429 12 runners 6f Standard To Slow ATR 02:00 6f 3429 HCP 4 FLT NOT
02:30
GAC SHIPPING AND LOGISTICS MAIDEN AUCTION STAKES (CLASS 6) (2yo) Winner £1,704 12 runners 1m Standard To Slow ATR 02:30 1m 1704 STKS 6 FLT MDN
03:00
BAKER TILLY CLASSIFIED CLAIMING STAKES (CLASS 6) (3yo+ 0-70) Winner £1,704 14 runners 7f Standard To Slow ATR 03:00 7f 1704 STKS 6 FLT NOT
03:35
NATWEST IRISH STALLION FARMS EBF MAIDEN STAKES (CLASS 5) (2yo) Winner £3,235 14 runners 7f Standard To Slow ATR 03:35 7f 3235 STKS 5 FLT MDN
04:05
EUROPA QUALITY PRINT HANDICAP (CLASS 2) (3yo 0-100) Winner £10,221 11 runners 1m4f Standard To Slow ATR 04:05 1m4f 10221 HCP 2 FLT NOT
04:40
CHARTPLAN HANDICAP (CLASS 6) (3yo+ 0-65) Winner £1,704 13 runners 2m Standard To Slow ATR 04:40 2m 1704 HCP 6 FLT NOT
05:10
GP LONDON HANDICAP (DIV I) (CLASS 5) (3yo+ 0-75) Winner £2,386 12 runners 1m Standard To Slow ATR 05:10 1m 2386 HCP 5 FLT NOT
05:45
GP LONDON HANDICAP (DIV II) (CLASS 5) (3yo+ 0-75) Winner £2,386 12 runners 1m Standard To Slow ATR 05:45 1m 2386 HCP 5 FLT NOT
04:20
BATHWICK TYRES BATH NOVICES' HANDICAP HURDLE (CLASS 4) (3yo+ 0-115) Winner £2,599 10 runners 2m4f Good To Soft RUK 04:20 2m4f 2599 HCP 4 HDL NOT
04:50
BATHWICK TYRES BRIDGWATER HANDICAP HURDLE (CLASS 4) (4yo+ 0-110) Winner £2,599 20 runners 2m Good To Soft RUK 04:50 2m 2599 HCP 4 HDL NOT
05:20
BATHWICK TYRES MIDSOMER NORTON HANDICAP CHASE (CLASS 5) (4yo+ 0-100) Winner £2,274 11 runners 3m1f110y Good To Soft RUK 05:20 3m1f110y 2274 HCP 5 CHS NOT
05:50
BATHWICK TYRES DORCHESTER MARES' STANDARD OPEN NATIONAL HUNT FLAT RACE (CLASS 6) (4-6yo) Winner £1,365 12 runners 2m Good To Soft ATRL 05:50 2m 1365 STKS 6 NHF NOT


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
hard to see the format here. Could you please upload a sample book at some public file share site and post back the link to the file back here.
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012

Thanks for the reply Rizvisa1,If you can log on to the Excel Forum website,in advanced search type "Extract data from text string to columns" it will take you to search boxes use keyword as above ,my username bigband1 and verification words,it will take you to the same question with an attached excel spreadsheet.
Hope this helps,Many thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
cannot download