General Betting
There is currently 1 person viewing this thread.
Hi, I have a list of horse names in excel with their country code in brackets such as:
Horse Name (IRE)
..and would like to remove all the info after the horse name so I am just left with the horse name.
Is there any code I could use to do this please?
Thank you in advance.
.
Horse Name (IRE)
..and would like to remove all the info after the horse name so I am just left with the horse name.
Is there any code I could use to do this please?
Thank you in advance.
.
sort by:
Show
per page
Replies: 24
|
By:
If you go to the Data tab, click on it you will see a button called Text to Columns. it is fairly self explanatory once clicked on. You want the "Delimited" option.
|
|
By:
but make sure to go back into it after using and clear the instruction, or it will apply to the next thing you copy and paste
|
|
By:
can you do find and replace to do this
|
|
By:
You could but unless I am mistaken you would need to enter every country code individually.
|
|
By:
Use the LEN function to return the length of the name including the country.
In the next cell deduct 5 from the value of LEN (3 letters + 2 brackets) Use the LEFT function to give you the text minus the 5 characters removed: Horses name in cell A1 FORUMITE (IRE) In B1 formula is LEN (A1) = 14 In C1 formula =(B1-5) = 9 In D1 formula is =LEFT (A1,C1) = FORUMITE (the first 9 characters of A1) |
|
By:
That seems overly complicated to me.
Text to Columns tab, Delimited, Delimiters "Other" use the first bracket (. Job done, delete the column with the country codes in if not required. |
|
By:
and not all country codes are 3 letters depending on the data provider e.g ATR use GB in Irish racing, FR etc
|
|
By:
If your horses name is in A1, put this formula into A2 & coppy down for other cells:
=LEFT(A1,(SEARCH("(",A1)-2)) |
|
By:
=IF(ISNUMBER(FIND("(",A1,1)),LEFT(A1,FIND("(",A1,1)-2),A1)
|
|
By:
just use find and replace it's a lot easier than all the faffing around
press control + H together , where it says Find what: stick in (*) then press the button sayng Replace All |
|
By:
Ghetto Joe ~ You win, best solution!
|
|
By:
Well maybe except you have to do it separately for (UK), (IRE), (FRA), (USA) and then check you havent missed any (RSA), (ARG) etc
|
|
By:
If you use Ghetto Joe's (*) change to (*)*
|
|
By:
Why the obsession with making a simple text splitting exercise, that requires about four or five mouse clicks, so complicated?
|
|
By:
didnt realise the * covers the bracket contents,but doesnt seem to be working even with (*)* ??
|
|
By:
Sorry (*) etc should work, but the data provider I tested it on limits the characters column length for the horses name
e.g HAIL PROMENADER (IRE |
|
By:
in that case just do another find replace for (* ,starfish
|
|
By:
Problem with text to columns is you may have other data in the next columns, Equimine.co.uk.
Sometimes having only a limited knowledge of excel is a bonus , Compound Magic :) |
|
By:
Never heard of inserting a column!
|
|
By:
Or for that matter just don't import the data you don't want. It really is very simple.
|
|
By:
Well i've always used that method equimime,just 6 clicks does it including add/remove column.
But as far as I can tell the 'dont import column' option just leaves you with the suffix, and not the horse name. And you do have to reset it afterwards otherwise it will do text to columns removing brackets for anything else you copy and paste on the sheet. |
|
By:
Thanks very much for all the replies, I will try and see what I can do this evening.
Good luck and thanks again. . |
|
By:
sandc
You are making sure you don't import the "suffix" column, not the original column which would have the horse name in the case requested. The copy paste scenario is certainly nothing I have come across. |
|
By:
Thanks again, especially Aye Robot. Your method was very simple, cheers.
. |