Extract text from variable string length

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
Hi,

I have a text string in Cell A1 that is populated by a program representing the Country, Course, Time and Distance etc of horse races and I want to extract the Course ie; Ling, Tampa, Redc & Distance elements ie; 5f, 1m, 2m4f, 2400m, 1780m etc into a variable for use in a Lookup

The string is of variable format and length and non UK races have a Country code in brackets.

There will only ever be 1 string (race) in Cell A1 at any one time, The external program will change Cell A1 when I ask it manually.

Here are some string examples that will appear in Cell A1:

Ling 11th Apr - 13:40 1m Hcap
MrktR 11th Apr - 14:00 2m Nov Hrd
MrktR 11th Apr - 14:30 2m4f Mdn Hrd
Ling 11th Apr - 16:50 1m4f Nov Stks
Dund 11th Apr - 20:30 1m2f Hcap
ChelmC 12th Apr - 17:45 5f Nov Stks
Long (FRA) 11th Apr - 11:55 1000m 2yo Claim
MontDe (FRA) 11th Apr - 16:10 2400m 4yo+ Hcap
Long (FRA) 11th Apr - 13:05 1600m 3yo Mdn
Redc (AUS) 11th Apr - 08:27 R1 1780m Pace M
Asct (AUS) 11th Apr - 09:10 R6 1600m CL5
Ipsw (AUS) 11th Apr - 07:38 R10 1350m Hcap
Geel (AUS) 11th Apr - 13:50 R11 2100m Pace M
Tampa (US) 11th Apr - 18:10 R2 7f Mdn Claim
Keenl (US) 11th Apr - 18:05 R1 1m1f Claim
Tampa (US) 11th Apr - 18:45 R3 6f Mdn Claim
Valpa (CHL) 11th Apr - 22:39 R10 1200m Hcap

So the extracted variable would look like this:

Ling1m
Ling1m4f
MontDe2400m
Keenl1m1f

etc

Can anyone write a VBA code to extract the string for me
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I wrote a formula:


Book1
AB
1Ling 11th Apr - 13:40 1m HcapLing1m
2MrktR 11th Apr - 14:00 2m Nov HrdMrktR2m
3MrktR 11th Apr - 14:30 2m4f Mdn HrdMrktR2m4f
4Ling 11th Apr - 16:50 1m4f Nov StksLing1m4f
5Dund 11th Apr - 20:30 1m2f HcapDund1m2f
6ChelmC 12th Apr - 17:45 5f Nov StksChelmC5f
7Long (FRA) 11th Apr - 11:55 1000m 2yo ClaimLong1000m
8MontDe (FRA) 11th Apr - 16:10 2400m 4yo+ HcapMontDe2400m
9Long (FRA) 11th Apr - 13:05 1600m 3yo MdnLong1600m
10Redc (AUS) 11th Apr - 08:27 R1 1780m Pace MRedc1780m
11Asct (AUS) 11th Apr - 09:10 R6 1600m CL5Asct1600m
12Ipsw (AUS) 11th Apr - 07:38 R10 1350m HcapIpsw1350m
13Geel (AUS) 11th Apr - 13:50 R11 2100m Pace MGeel2100m
14Tampa (US) 11th Apr - 18:10 R2 7f Mdn ClaimTampa7f
15Keenl (US) 11th Apr - 18:05 R1 1m1f ClaimKeenl1m1f
16Tampa (US) 11th Apr - 18:45 R3 6f Mdn ClaimTampa6f
17Valpa (CHL) 11th Apr - 22:39 R10 1200m HcapValpa1200m
Sheet1
Cell Formulas
RangeFormula
B1=LEFT($A1,FIND(" ",$A1)-1)&TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),(5+ISNUMBER(FIND(" (",$A1))+ISNUMBER(FIND(" R",$A1)))*LEN($A1)+1,LEN($A1)))


If you want VBA, it's reasonably straightforward too.

WBD
 
Upvote 0
Hi.

You need to be able to establish a pattern for this, which I think is possible, and if you can do that, you can do it with a simple text formula.

QUESTION - what should the result be for these inputs ?

Redc (AUS) 11th Apr - 08:27 R1 1780m Pace M
Asct (AUS) 11th Apr - 09:10 R6 1600m CL5
Ipsw (AUS) 11th Apr - 07:38 R10 1350m Hcap
Geel (AUS) 11th Apr - 13:50 R11 2100m Pace M
Tampa (US) 11th Apr - 18:10 R2 7f Mdn Claim
Keenl (US) 11th Apr - 18:05 R1 1m1f Claim
Tampa (US) 11th Apr - 18:45 R3 6f Mdn Claim
Valpa (CHL) 11th Apr - 22:39 R10 1200m Hcap

Specifically, should the R1 / R3 etc element be included in the result ?
 
Upvote 0
Hi, thanks for the formula.

It works on all Races that have a Country code in parenthesis, FRA, US, AUS but on the strings that do not have parenthesis in them it is appending the race time xx:xx to the course name :

MrktR 11th Apr - 14:30 2m4f Mdn Hrd -> MrktR14:30
Ling 11th Apr - 16:50 1m4f Nov Stks -> Ling16:50

but this type are ok:
Keenl (US) 11th Apr - 18:05 R1 1m1f Claim -> Keenl1m1f

I know there are some double spaces in some of the strings, and not in others, could that be it ?
 
Upvote 0
Hi.

You need to be able to establish a pattern for this, which I think is possible, and if you can do that, you can do it with a simple text formula.

QUESTION - what should the result be for these inputs ?

Redc (AUS) 11th Apr - 08:27 R1 1780m Pace M
Asct (AUS) 11th Apr - 09:10 R6 1600m CL5
Ipsw (AUS) 11th Apr - 07:38 R10 1350m Hcap
Geel (AUS) 11th Apr - 13:50 R11 2100m Pace M
Tampa (US) 11th Apr - 18:10 R2 7f Mdn Claim
Keenl (US) 11th Apr - 18:05 R1 1m1f Claim
Tampa (US) 11th Apr - 18:45 R3 6f Mdn Claim
Valpa (CHL) 11th Apr - 22:39 R10 1200m Hcap

Specifically, should the R1 / R3 etc element be included in the result ?


Hi, the above races should result in:

Redc1780m
Asct1600m
Ipsw1350m
Geel2100m
Tampa7f
Keenl1m1f
Tampa6f
Valpa1200m

The R1 / R3 etc numbers are ignored

thanks
 
Upvote 0
Double spaces will certainly throw out the formula. Here's some code:

Code:
Public Function ExtractRaceText(inputString As String) As String

Dim stringParts As Variant
Dim lengthPart As Long

' Remove double spaces
While InStr(1, inputString, "  ") > 0
    inputString = Replace(inputString, "  ", " ")
Wend

' Split at the spaces
stringParts = Split(inputString, " ")

' Get the courst name
ExtractRaceText = stringParts(0)

' Default length is the 5th word
lengthPart = 5

' Add on one if there's a country code
If Left$(stringParts(1), 1) = "(" Then lengthPart = lengthPart + 1

' Add on one if the length starts with "R"
If Left$(stringParts(lengthPart), 1) = "R" Then lengthPart = lengthPart + 1

' Add on the length part to the course name
ExtractRaceText = ExtractRaceText & stringParts(lengthPart)

End Function
Public Sub ExtractAllRaceText()

Dim lastRow As Long
Dim thisRow As Long

' Get the last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Process all values in column A
For thisRow = 1 To lastRow
    ' Extract the race text into column B
    Cells(thisRow, "B").Value = ExtractRaceText(Cells(thisRow, "A").Value)
Next thisRow

End Sub


Book1
AB
1Ling 11th Apr - 13:40 1m HcapLing1m
2MrktR 11th Apr - 14:00 2m Nov HrdMrktR2m
3MrktR 11th Apr - 14:30 2m4f Mdn HrdMrktR2m4f
4Ling 11th Apr - 16:50 1m4f Nov StksLing1m4f
5Dund 11th Apr - 20:30 1m2f HcapDund1m2f
6ChelmC 12th Apr - 17:45 5f Nov StksChelmC5f
7Long (FRA) 11th Apr - 11:55 1000m 2yo ClaimLong1000m
8MontDe (FRA) 11th Apr - 16:10 2400m 4yo+ HcapMontDe2400m
9Long (FRA) 11th Apr - 13:05 1600m 3yo MdnLong1600m
10Redc (AUS) 11th Apr - 08:27 R1 1780m Pace MRedc1780m
11Asct (AUS) 11th Apr - 09:10 R6 1600m CL5Asct1600m
12Ipsw (AUS) 11th Apr - 07:38 R10 1350m HcapIpsw1350m
13Geel (AUS) 11th Apr - 13:50 R11 2100m Pace MGeel2100m
14Tampa (US) 11th Apr - 18:10 R2 7f Mdn ClaimTampa7f
15Keenl (US) 11th Apr - 18:05 R1 1m1f ClaimKeenl1m1f
16Tampa (US) 11th Apr - 18:45 R3 6f Mdn ClaimTampa6f
17Valpa (CHL) 11th Apr - 22:39 R10 1200m HcapValpa1200m
Sheet1
Cell Formulas
RangeFormula
B1=ExtractRaceText($A1)


You can either use the function in the cell formula as shown or you could call the Sub which will extract the information as plain text and put it in column B.

WBD
 
Upvote 0
That works great, thank you

Sorry for not replying sooner I was out yesterday

Much appreciated

All I have to do now is work out what it does so I'll know for the future !

cheers
 
Upvote 0
Just gone through your code and worked out what you did, very clever and I've learnt some new functions now, so thanks again.

One further question:

From a performance perspective, ie speed of execution and demand on the spreadsheet, on a sheet that constantly monitors changes & recalculates is it better to use Formulas or vba routines to achieve the same result ? ie your 1st solution used formulas, would that have been a preferred solution rather than vba code from a speed perspective ?
 
Upvote 0
Depends on whether you want it to update dynamically or whether you want to be in charge. The function should be quite quick so using the formula wouldn't be too onerous anyway.

WBD
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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