Assistance required

[mdb231]

Hi all I am into horseracing and download results via a program which someone did for me, the thing I would like assistance with, is when the names get downloaded they are have in brackets following the name the country where the horse is bred.,but when I do a search it can miss some because of this.
Is there a macro or something that will take these off so all that is left is the name or the horse.
Any help appreciated.
Michael

 

[Joe4]

Please post an example of what the data looks like, and what you want your expected results to look like.

 

[mdb231]

Ok will do that later when I get home

 

[mdb231]

Fast And Hot (IRE)
Bird For Life
General Brook (IRE)
Hong Kong Joe
Munsarim (IRE)
Joshlee (IRE)
Thahab Ifraj (IRE)
Garcon De Soleil
Xhale (FR)
Roy Rocket (FR)
Takeitfromalady (IRE)
Lady Of York
Iley Boy
Warofindependence (USA)
Hi There Silver (IRE)
Pack It In (IRE)
Feel The Vibes
Spiritofedinburgh (IRE)
Patent
Santadelacruze
Bookmaker
Rattle On
The King's Steed (ex6)
Bertie Blu Boy
Ertidaad (IRE)
Mowhoob
Tarseekh
Rocksette
Caius College Girl (IRE)
How's Lucy
Excellent World (IRE)
Mime Dance
De Little Engine (IRE)

Anything with the brackets needs to go, can occasionally contain numbers. Sorry it's not in excel but long time since I was on here and have forgotten how to add a file from excel.
Hope this helps.

 

[Joe4]

Assuming your first value starts in cell A1, use this formula and copy down for all rows:

=IF(ISNUMBER(FIND("(",A1)),TRIM(LEFT(A1,FIND("(",A1)-1)),A1)

 

[mdb231]

Thank you for the quick reply will try it out.

 

[mdb231]

Works a treat.
Thank you Joe4

 

[Joe4]

You are welcome.

If you would like to replace the original data instead of having a new column, you can use Text to Columns, using the "(" as your delimiter.
If you do that with VBA, you can do it even quicker, simply by running the macro. That code would look like:

Sub CropData()

    Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="(", FieldInfo:=Array(Array(1, 1), Array(2, 9)), TrailingMinusNumbers:=True

End Sub

 

[jtakw]

Hi,

Another formula option:

Excel 2010
	A	B
1	Fast And Hot (IRE)	Fast And Hot
2	Bird For Life	Bird For Life
3	General Brook (IRE)	General Brook
4	Hong Kong Joe	Hong Kong Joe
5	Munsarim (IRE)	Munsarim
6	Joshlee (IRE)	Joshlee
7	Thahab Ifraj (IRE)	Thahab Ifraj
8	Garcon De Soleil	Garcon De Soleil
9	Xhale (FR)	Xhale
10	Roy Rocket (FR)	Roy Rocket
11	Takeitfromalady (IRE)	Takeitfromalady
12	Lady Of York	Lady Of York
13	Iley Boy	Iley Boy
14	Warofindependence (USA)	Warofindependence
15	Hi There Silver (IRE)	Hi There Silver
16	Pack It In (IRE)	Pack It In
17	Feel The Vibes	Feel The Vibes
18	Spiritofedinburgh (IRE)	Spiritofedinburgh
19	Patent	Patent
20	Santadelacruze	Santadelacruze
21	Bookmaker	Bookmaker
22	Rattle On	Rattle On
23	The King's Steed (ex6)	The King's Steed
24	Bertie Blu Boy	Bertie Blu Boy
25	Ertidaad (IRE)	Ertidaad
26	Mowhoob	Mowhoob
27	Tarseekh	Tarseekh
28	Rocksette	Rocksette
29	Caius College Girl (IRE)	Caius College Girl
30	How's Lucy	How's Lucy
31	Excellent World (IRE)	Excellent World
32	Mime Dance	Mime Dance
33	De Little Engine (IRE)	De Little Engine
Sheet16

Cell Formulas
Range	Formula
B1	=TRIM(REPLACE(A1,FIND("(",A1&"("),255,""))

B1 formula copied down.

 

[Peter_SSs]

If you would like to replace the original data instead of having a new column, you can use Text to Columns, ..

.. or similar to Joe's idea but even more simply (to me at least) with Find/Replace with " (*)" (without the "") typed into the Find What box & nothing in the Replace with box & not matching entire contents. As a macro that would be

Sub remove_country()
  Range("A2", Range("A" & Rows.Count).End(xlUp)).Replace What:=" (*)", Replacement:="", LookAt:=xlPart
End Sub

Edit: Or if you did want to keep the original data and put the results in the next column

Sub remove_country_v2()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",left(#,find(""("",#&"" ("")-1))", "#", .Address))
  End With
End Sub