Extract text from variable string length

[Billm]

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

 

[wideboydixon]

I wrote a formula:

Book1
	A	B
1	Ling 11th Apr - 13:40 1m Hcap	Ling1m
2	MrktR 11th Apr - 14:00 2m Nov Hrd	MrktR2m
3	MrktR 11th Apr - 14:30 2m4f Mdn Hrd	MrktR2m4f
4	Ling 11th Apr - 16:50 1m4f Nov Stks	Ling1m4f
5	Dund 11th Apr - 20:30 1m2f Hcap	Dund1m2f
6	ChelmC 12th Apr - 17:45 5f Nov Stks	ChelmC5f
7	Long (FRA) 11th Apr - 11:55 1000m 2yo Claim	Long1000m
8	MontDe (FRA) 11th Apr - 16:10 2400m 4yo+ Hcap	MontDe2400m
9	Long (FRA) 11th Apr - 13:05 1600m 3yo Mdn	Long1600m
10	Redc (AUS) 11th Apr - 08:27 R1 1780m Pace M	Redc1780m
11	Asct (AUS) 11th Apr - 09:10 R6 1600m CL5	Asct1600m
12	Ipsw (AUS) 11th Apr - 07:38 R10 1350m Hcap	Ipsw1350m
13	Geel (AUS) 11th Apr - 13:50 R11 2100m Pace M	Geel2100m
14	Tampa (US) 11th Apr - 18:10 R2 7f Mdn Claim	Tampa7f
15	Keenl (US) 11th Apr - 18:05 R1 1m1f Claim	Keenl1m1f
16	Tampa (US) 11th Apr - 18:45 R3 6f Mdn Claim	Tampa6f
17	Valpa (CHL) 11th Apr - 22:39 R10 1200m Hcap	Valpa1200m
Sheet1

Cell Formulas
Range	Formula
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

 

[Gerald Higgins]

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 ?

 

[Billm]

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 ?

 

[Billm]

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

 

[wideboydixon]

Double spaces will certainly throw out the formula. Here's some 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
	A	B
1	Ling 11th Apr - 13:40 1m Hcap	Ling1m
2	MrktR 11th Apr - 14:00 2m Nov Hrd	MrktR2m
3	MrktR 11th Apr - 14:30 2m4f Mdn Hrd	MrktR2m4f
4	Ling 11th Apr - 16:50 1m4f Nov Stks	Ling1m4f
5	Dund 11th Apr - 20:30 1m2f Hcap	Dund1m2f
6	ChelmC 12th Apr - 17:45 5f Nov Stks	ChelmC5f
7	Long (FRA) 11th Apr - 11:55 1000m 2yo Claim	Long1000m
8	MontDe (FRA) 11th Apr - 16:10 2400m 4yo+ Hcap	MontDe2400m
9	Long (FRA) 11th Apr - 13:05 1600m 3yo Mdn	Long1600m
10	Redc (AUS) 11th Apr - 08:27 R1 1780m Pace M	Redc1780m
11	Asct (AUS) 11th Apr - 09:10 R6 1600m CL5	Asct1600m
12	Ipsw (AUS) 11th Apr - 07:38 R10 1350m Hcap	Ipsw1350m
13	Geel (AUS) 11th Apr - 13:50 R11 2100m Pace M	Geel2100m
14	Tampa (US) 11th Apr - 18:10 R2 7f Mdn Claim	Tampa7f
15	Keenl (US) 11th Apr - 18:05 R1 1m1f Claim	Keenl1m1f
16	Tampa (US) 11th Apr - 18:45 R3 6f Mdn Claim	Tampa6f
17	Valpa (CHL) 11th Apr - 22:39 R10 1200m Hcap	Valpa1200m
Sheet1

Cell Formulas
Range	Formula
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

 

[Billm]

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

 

[wideboydixon]

That's why I added comments; hopefully that helps with your understanding.

WBD

 

[Billm]

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 ?

 

[wideboydixon]

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