Parse Horsename

S

Saxman

Below is an example of how horsenames are presented.

I need to omit the last two letters/numbers after the horsename.

It's made awkward owing to the fact that horsenames often contain spaces
within the name.

Baron Run h1
Blazeofenchantment p
Danehill Flyer 6x
Klynch b
Orbit The Moon tp
Queen Flush b1


TIA
 
R

Ron Rosenfeld

Below is an example of how horsenames are presented.

I need to omit the last two letters/numbers after the horsename.

It's made awkward owing to the fact that horsenames often contain spaces
within the name.

Baron Run h1
Blazeofenchantment p
Danehill Flyer 6x
Klynch b
Orbit The Moon tp
Queen Flush b1


TIA
Click to expand...


=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

will return everything EXCEPT the last substring.
 
M

Michael Bednarek

Below is an example of how horsenames are presented.

I need to omit the last two letters/numbers after the horsename.

It's made awkward owing to the fact that horsenames often contain spaces
within the name.

Baron Run h1
Blazeofenchantment p
Danehill Flyer 6x
Klynch b
Orbit The Moon tp
Queen Flush b1
Click to expand...

=TRIM(LEFT(A1,LEN(A1)-2))
TRIM() is not strictly necessary. If there were always 2 characters to
be removed, the formula would be
=LEFT(A1,LEN(A1)-3)
removing those 2 plus the last blank. But because sometimes just 1
character is to be removed, this formula always removes 2, leaving a
trailing blank in most cases, which is removed by TRIM().

If Excel would provide the function InstrRev(), this could be written
more generally as
=Left(A1,InstrRev(A1," ")-1)
to return a string up to the last blank, regardless how many characters
follow. InstrRev() can of course be implemented as a User Defined
Function.
 
S

Saxman

=LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

will return everything EXCEPT the last substring.
Click to expand...


It returns the following.

Arcadian Legend Arcadian
Black Sceptre Black
Cape Factor Cape
Copper Trade Copper
Daneglow #VALUE!
Dawn Rock Dawn
Dazzling Valentine Dazzling
Delightful Sleep Delightful
Delphica v #VALUE!
Double Star Double
East Texas Red East Texas
Flashy Star Flashy
Flicksta #VALUE!
Go Amwell v Go
Handsome Stranger v Handsome
Hi Note Hi
High Tone High
Hilden b #VALUE!
 
R

Ron Rosenfeld

It returns the following.

Arcadian Legend Arcadian
Black Sceptre Black
Cape Factor Cape
Copper Trade Copper
Daneglow #VALUE!
Dawn Rock Dawn
Dazzling Valentine Dazzling
Delightful Sleep Delightful
Delphica v #VALUE!
Double Star Double
East Texas Red East Texas
Flashy Star Flashy
Flicksta #VALUE!
Go Amwell v Go
Handsome Stranger v Handsome
Hi Note Hi
High Tone High
Hilden b #VALUE!
Click to expand...

Of course you will get undesired results when you try to apply a formula designed for your original request and examples to a set of data which is dissimilar.

Note that in your original set of examples, every single instance had either one or two letters/numbers at the end to be removed (which was your request).
Perhaps you need to rethink what you want done.

The following is just a guess. It will remove the last "word" from the string if and only if that last "word" has two or fewer characters:

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))<=2,
LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))-1),A1)

Howver, if the horse has two names, and the last name is one or two characters, it will remove that.

In other words.
High Tone 2B --> High Tone

but if the name is
Hilden B --> Hilden

Without further rules which you would need to provide, and are not apparent from any of your examples, there would be no way to differentiate a "last name" of one or two characters, from an undesired suffix.

Futhermore, my original formula will NOT return a #VALUE error for "Hilden b" or "Delphica v" unless the character preceding the "b" or the "v" is NOT a <space>. However, it is a <space> in your posted example. (In this latest iteration, they should be returned "whole")
 
S

Saxman

Of course you will get undesired results when you try to apply a formula designed for your original request and examples to a set of data which is dissimilar.

Note that in your original set of examples, every single instance had either one or two letters/numbers at the end to be removed (which was your request).
Perhaps you need to rethink what you want done.

The following is just a guess. It will remove the last "word" from the string if and only if that last "word" has two or fewer characters:

=IF(LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))<=2,
LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(
SUBSTITUTE(A1," ",""))))-1),A1)

Howver, if the horse has two names, and the last name is one or two characters, it will remove that.

In other words.
High Tone 2B --> High Tone

but if the name is
Hilden B --> Hilden

Without further rules which you would need to provide, and are not apparent from any of your examples, there would be no way to differentiate a "last name" of one or two characters, from an undesired suffix.

Futhermore, my original formula will NOT return a #VALUE error for "Hilden b" or "Delphica v" unless the character preceding the "b" or the "v" is NOT a <space>. However, it is a <space> in your posted example. (In this latest iteration, they should be returned "whole")
Click to expand...

It doesn't do anything Ron; just returns the same values.

Here is an example of today's Yarmouth card.

Absolute Diamond 42 p
Aleksandar 8
Baltic Spirit 11
Barleycorn 52
Birdy Boy 5
Black Treacle 8
Blue Shoes 23
Bounty Seeker 12
Copybook
Cracking Choice 12 b
Cymeriad 18 p
Dansili Dutch 11
Deepest Blue 12
Dhhamaan 12 b
Dialogue 19 t
Don´t Tell 40
Dr Irv 12
Durham Express 8
Forever Janey 58
Harbinger Lass 11
Jontleman 11
Jupiter Fidius 26
Just Paul 4 6x
Just The Tonic 34
Karate Queen 23 p
Keep It Cool 7
Kindanyce 26
Lady Alaska 21
Lees Anthem 16
Logans Legend 7 p
Madame Giry h1
Magic Skyline 12
Major Domo 35
Marsden Cuckoo
Mason Hindmarsh 16
Masterful Act 12 p
Money Team 11
Multisure 4
Mysterious Wonder 53
Needy McCredie 23
No Quarter 40
Northern Meeting 58
Patisserie 7 p
Perfect Words 18 p
Pippy 29 p
Porsh Herrik 28 v1
Prince Of Vasa 21 t
Rasselas 5 6xp
Rat Catcher 16 b
Red Orator 19
Refuse Colette 96
Rich Forever 5 p
Saffron Town 147J t
Scala Romana 7
Silkelly 23 h
Snow Train 12
Society Pearl 14
Star City 12 eb
Storma Norma 13 h
Strong Man 12 b
Sylvia Pankhurst 33 p
This Is Nice 8 p
Thrust Control 15 v1
Tony Hollis 5
Ursus 16 p
Whistle We Go 27
Windforpower 1
Wynyard Boy 52
 
C

Claus Busch

Hi,

Am Wed, 10 Jul 2013 16:38:00 +0100 schrieb Saxman:

It doesn't do anything Ron; just returns the same values.
Click to expand...
Absolute Diamond 42 p
Saffron Town 147J t
Click to expand...

what is the expected result for the names above?
Absolute Diamond or Absolute Diamond 42?
Saffron Town or Saffron Town 147J?
Try following function to eleminate the last to digits:

Function ParseHorsename(myCell As Range) As String
Dim myEnd As Integer

myEnd = InStrRev(myCell, " ")
ParseHorsename = IIf(myEnd >= Len(myCell) - 2, _
Mid(myCell, 1, InStrRev(myCell, " ")), myCell)
End Function

If your names are in column A, then in B =ParseHorsename(A1)


Regards
Claus B.
 
S

Saxman

Hi,

Am Wed, 10 Jul 2013 16:38:00 +0100 schrieb Saxman:




what is the expected result for the names above?
Absolute Diamond or Absolute Diamond 42?
Saffron Town or Saffron Town 147J?
Try following function to eleminate the last to digits:

Function ParseHorsename(myCell As Range) As String
Dim myEnd As Integer

myEnd = InStrRev(myCell, " ")
ParseHorsename = IIf(myEnd >= Len(myCell) - 2, _
Mid(myCell, 1, InStrRev(myCell, " ")), myCell)
End Function

If your names are in column A, then in B =ParseHorsename(A1)


Regards
Claus B.
Click to expand...

Absolute Diamond and Saffron Town is what I require. With the
extraneous data on the end, I cannot merge the data.

Function ParseHorsename(myCell As Range) As String
Dim myEnd As Integer

myEnd = InStrRev(myCell, " ")
ParseHorsename = IIf(myEnd >= Len(myCell) - 2, _
Mid(myCell, 1, InStrRev(myCell, " ")), myCell)
End Function

I have pasted the above into Visual Basic, but cannot get it to work?
 
V

Vidcapper

Of course you will get undesired results when you try to apply a formula designed for your original request and examples to a set of data which is dissimilar.
Click to expand...

If only a spreadsheet could predict the *winners*... :p
 
C

Claus Busch

Hi,

Am Wed, 10 Jul 2013 17:11:08 +0100 schrieb Saxman:
Absolute Diamond and Saffron Town is what I require. With the
extraneous data on the end, I cannot merge the data.
Click to expand...

forget the function. It brings false results. You have to call it from
the workbook with =Parsehorsename(A1)

Try:
=LEFT(A1,MATCH(1,ISNUMBER(MID(A1&0,COLUMN(1:1),1)*1)*1,0)-1)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
 
S

Saxman

Hi,

Am Wed, 10 Jul 2013 17:11:08 +0100 schrieb Saxman:


forget the function. It brings false results. You have to call it from
the workbook with =Parsehorsename(A1)

Try:
=LEFT(A1,MATCH(1,ISNUMBER(MID(A1&0,COLUMN(1:1),1)*1)*1,0)-1)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
Click to expand...


Not sure about this. Do I forget the code and put the above in B1?
 
C

Claus Busch

Hi again,

Am Wed, 10 Jul 2013 17:39:52 +0100 schrieb Saxman:
Not sure about this. Do I forget the code and put the above in B1?
Click to expand...

yes, delete the code and try the array formula in B1


Regards
Claus B.
 
S

Saxman

Hi,

Am Wed, 10 Jul 2013 17:11:08 +0100 schrieb Saxman:


forget the function. It brings false results. You have to call it from
the workbook with =Parsehorsename(A1)

Try:
=LEFT(A1,MATCH(1,ISNUMBER(MID(A1&0,COLUMN(1:1),1)*1)*1,0)-1)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
Click to expand...

=LEFT(A1,MATCH(1,ISNUMBER(MID(A1&0,COLUMN(1:1),1)*1)*1,0)-1)

If I put the above in B1, I get #N/A for all examples.
 
R

Ron Rosenfeld

It doesn't do anything Ron; just returns the same values.

Here is an example of today's Yarmouth card.
Click to expand...

Although I do not expect the formula to work in all of the instances in your new example, which, again, contains a number of names in formats that do NOT MATCH anything you had provided previously, you must be doing something incorrect, When I ran it here, it returned the following results, on your new list:

Absolute Diamond 42 p Absolute Diamond 42
Aleksandar 8 Aleksandar
Baltic Spirit 11 Baltic Spirit
Barleycorn 52 Barleycorn
Birdy Boy 5 Birdy Boy
Black Treacle 8 Black Treacle
Blue Shoes 23 Blue Shoes
Bounty Seeker 12 Bounty Seeker
Copybook Copybook
Cracking Choice 12 b Cracking Choice 12
Cymeriad 18 p Cymeriad 18
Dansili Dutch 11 Dansili Dutch
Deepest Blue 12 Deepest Blue
Dhhamaan 12 b Dhhamaan 12
Dialogue 19 t Dialogue 19
Don´t Tell 40 Don´t Tell
Dr Irv 12 Dr Irv
Durham Express 8 Durham Express
Forever Janey 58 Forever Janey
Harbinger Lass 11 Harbinger Lass
Jontleman 11 Jontleman
Jupiter Fidius 26 Jupiter Fidius
Just Paul 4 6x Just Paul 4
Just The Tonic 34 Just The Tonic
Karate Queen 23 p Karate Queen 23
Keep It Cool 7 Keep It Cool
Kindanyce 26 Kindanyce
Lady Alaska 21 Lady Alaska

Since you got the same value in each of those, there must be something wrong with your worksheet, or your environment. Until you determine what that problem is, you will have a difficult time applying any solution that is put forth.
I have no idea what might be the issue, as the formula works as designed, and as was suggested by your previous examples.

But now it seems you have yet another type of format which might involve more than just removing the last two characters. I wonder if you will ever supply us with a comprehensive list; and I continue to be amazed at your expectations that providing us with examples that do not reflect what you are truly dealing with, will result in a solution to your problem.

In any event, after you figure out what is wrong with your environment, such that my last formula "doesn't do anything; just returns the same values", you might want to try this User Defined Function.

But PLEASE let me know the problem that resulted in my formula just returning the same values. It will be useful to me in the future. I would suggest ensuring that you have calculation mode set to automatic, but beyond that I am not sure.

Follow these instructions very carefully to enter and use the UDF:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=HorseName(A1)

in some cell.

With all of the examples you have posted so far, in all of the various formats you have provided, this UDF returns what seems to be the desired result (see below)

========================================
Option Explicit
Function HorseName(s As String) As String
Dim re As Object
Const sPat As String = "(^.*?)(\s\w{1,2})*$"
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.MultiLine = False
.Pattern = sPat
HorseName = .Replace(s, "$1")
End With
End Function
===================================

Here are your provided examples, and the results using this UDF. If it is not working for you, re-read the instructions carefully.

Arcadian Legend Arcadian Legend
Black Sceptre Black Sceptre
Cape Factor Cape Factor
Copper Trade Copper Trade
Daneglow Daneglow
Dawn Rock Dawn Rock
Dazzling Valentine Dazzling Valentine
Delightful Sleep Delightful Sleep
Delphica v Delphica
Double Star Double Star
East Texas Red East Texas Red
Flashy Star Flashy Star
Flicksta Flicksta
Go Amwell v Go Amwell
Handsome Stranger v Handsome Stranger
Hi Note Hi Note
High Tone High Tone
Hilden b Hilden
Absolute Diamond 42 p Absolute Diamond
Aleksandar 8 Aleksandar
Baltic Spirit 11 Baltic Spirit
Barleycorn 52 Barleycorn
Birdy Boy 5 Birdy Boy
Black Treacle 8 Black Treacle
Blue Shoes 23 Blue Shoes
Bounty Seeker 12 Bounty Seeker
Copybook Copybook
Cracking Choice 12 b Cracking Choice
Cymeriad 18 p Cymeriad
Dansili Dutch 11 Dansili Dutch
Deepest Blue 12 Deepest Blue
Dhhamaan 12 b Dhhamaan
Dialogue 19 t Dialogue
Don´t Tell 40 Don´t Tell
Dr Irv 12 Dr Irv
Durham Express 8 Durham Express
Forever Janey 58 Forever Janey
Harbinger Lass 11 Harbinger Lass
Jontleman 11 Jontleman
Jupiter Fidius 26 Jupiter Fidius
Just Paul 4 6x Just Paul
Just The Tonic 34 Just The Tonic
Karate Queen 23 p Karate Queen
Keep It Cool 7 Keep It Cool
Kindanyce 26 Kindanyce
Lady Alaska 21 Lady Alaska
Baron Run h1 Baron Run
Blazeofenchantment p Blazeofenchantment
Danehill Flyer 6x Danehill Flyer
Klynch b Klynch
Orbit The Moon tp Orbit The Moon
Queen Flush b1 Queen Flush
 
R

Ron Rosenfeld

Hi,

Am Wed, 10 Jul 2013 17:11:08 +0100 schrieb Saxman:


forget the function. It brings false results. You have to call it from
the workbook with =Parsehorsename(A1)

Try:
=LEFT(A1,MATCH(1,ISNUMBER(MID(A1&0,COLUMN(1:1),1)*1)*1,0)-1)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
Click to expand...

Of course, you realize that will only work on the examples he gave in his last posting, wherein all of the extraneous characters began with a number. In his previous two examples, he had some apparently extraneous characters that began with a letter <sigh>.
 
S

Saxman

Although I do not expect the formula to work in all of the instances in your new example, which, again, contains a number of names in formats that do NOT MATCH anything you had provided previously, you must be doing something incorrect, When I ran it here, it returned the following results, on your new list:

Absolute Diamond 42 p Absolute Diamond 42
Aleksandar 8 Aleksandar
Baltic Spirit 11 Baltic Spirit
Barleycorn 52 Barleycorn
Birdy Boy 5 Birdy Boy
Black Treacle 8 Black Treacle
Blue Shoes 23 Blue Shoes
Bounty Seeker 12 Bounty Seeker
Copybook Copybook
Cracking Choice 12 b Cracking Choice 12
Cymeriad 18 p Cymeriad 18
Dansili Dutch 11 Dansili Dutch
Deepest Blue 12 Deepest Blue
Dhhamaan 12 b Dhhamaan 12
Dialogue 19 t Dialogue 19
Don´t Tell 40 Don´t Tell
Dr Irv 12 Dr Irv
Durham Express 8 Durham Express
Forever Janey 58 Forever Janey
Harbinger Lass 11 Harbinger Lass
Jontleman 11 Jontleman
Jupiter Fidius 26 Jupiter Fidius
Just Paul 4 6x Just Paul 4
Just The Tonic 34 Just The Tonic
Karate Queen 23 p Karate Queen 23
Keep It Cool 7 Keep It Cool
Kindanyce 26 Kindanyce
Lady Alaska 21 Lady Alaska

Since you got the same value in each of those, there must be something wrong with your worksheet, or your environment. Until you determine what that problem is, you will have a difficult time applying any solution that is put forth.
I have no idea what might be the issue, as the formula works as designed, and as was suggested by your previous examples.

But now it seems you have yet another type of format which might involve more than just removing the last two characters. I wonder if you will ever supply us with a comprehensive list; and I continue to be amazed at your expectations that providing us with examples that do not reflect what you are truly dealing with, will result in a solution to your problem.

In any event, after you figure out what is wrong with your environment, such that my last formula "doesn't do anything; just returns the same values", you might want to try this User Defined Function.

But PLEASE let me know the problem that resulted in my formula just returning the same values. It will be useful to me in the future. I would suggest ensuring that you have calculation mode set to automatic, but beyond that I am not sure.

Follow these instructions very carefully to enter and use the UDF:

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=HorseName(A1)

in some cell.

With all of the examples you have posted so far, in all of the various formats you have provided, this UDF returns what seems to be the desired result (see below)

========================================
Option Explicit
Function HorseName(s As String) As String
Dim re As Object
Const sPat As String = "(^.*?)(\s\w{1,2})*$"
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.MultiLine = False
.Pattern = sPat
HorseName = .Replace(s, "$1")
End With
End Function
===================================

Here are your provided examples, and the results using this UDF. If it is not working for you, re-read the instructions carefully.

Arcadian Legend Arcadian Legend
Black Sceptre Black Sceptre
Cape Factor Cape Factor
Copper Trade Copper Trade
Daneglow Daneglow
Dawn Rock Dawn Rock
Dazzling Valentine Dazzling Valentine
Delightful Sleep Delightful Sleep
Delphica v Delphica
Double Star Double Star
East Texas Red East Texas Red
Flashy Star Flashy Star
Flicksta Flicksta
Go Amwell v Go Amwell
Handsome Stranger v Handsome Stranger
Hi Note Hi Note
High Tone High Tone
Hilden b Hilden
Absolute Diamond 42 p Absolute Diamond
Aleksandar 8 Aleksandar
Baltic Spirit 11 Baltic Spirit
Barleycorn 52 Barleycorn
Birdy Boy 5 Birdy Boy
Black Treacle 8 Black Treacle
Blue Shoes 23 Blue Shoes
Bounty Seeker 12 Bounty Seeker
Copybook Copybook
Cracking Choice 12 b Cracking Choice
Cymeriad 18 p Cymeriad
Dansili Dutch 11 Dansili Dutch
Deepest Blue 12 Deepest Blue
Dhhamaan 12 b Dhhamaan
Dialogue 19 t Dialogue
Don´t Tell 40 Don´t Tell
Dr Irv 12 Dr Irv
Durham Express 8 Durham Express
Forever Janey 58 Forever Janey
Harbinger Lass 11 Harbinger Lass
Jontleman 11 Jontleman
Jupiter Fidius 26 Jupiter Fidius
Just Paul 4 6x Just Paul
Just The Tonic 34 Just The Tonic
Karate Queen 23 p Karate Queen
Keep It Cool 7 Keep It Cool
Kindanyce 26 Kindanyce
Lady Alaska 21 Lady Alaska
Baron Run h1 Baron Run
Blazeofenchantment p Blazeofenchantment
Danehill Flyer 6x Danehill Flyer
Klynch b Klynch
Orbit The Moon tp Orbit The Moon
Queen Flush b1 Queen Flush
Click to expand...


I'll give that a try as well.

Thanks.
 
A

Alex Plantema

Saxman schreef in
Below is an example of how horsenames are presented.

I need to omit the last two letters/numbers after the horsename.

It's made awkward owing to the fact that horsenames often contain
spaces within the name.

Baron Run h1
Blazeofenchantment p
Danehill Flyer 6x
Klynch b
Orbit The Moon tp
Queen Flush b1
Click to expand...

In OpenOffice or LibreOffice, select the cells with the horsenames,
then Edit, Find & Replace,
Search for: ^(.*) (.*)$
Replace with: $1
Under More Options, check Current selection only, Regular expressions.
Then press Replace All.
 
Top