Excel help please

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.

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

can you do find and replace to do this

You could but unless I am mistaken you would need to enter every country code individually.

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)

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.

and not all country codes are 3 letters depending on the data provider e.g ATR use GB in Irish racing, FR etc

If your horses name is in A1, put this formula into A2 & coppy down for other cells:

=LEFT(A1,(SEARCH("(",A1)-2))

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

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

Ghetto Joe ~ You win, best solution!

Well maybe except you have to do it separately for (UK), (IRE), (FRA), (USA) and then check you havent missed any (RSA), (ARG) etc

If you use Ghetto Joe's (*) change to (*)*

Why the obsession with making a simple text splitting exercise, that requires about four or five mouse clicks, so complicated?

didnt realise the * covers the bracket contents,but doesnt seem to be working even with (*)* ??

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

in that case just do another find replace for (* ,starfish

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 :)

Never heard of inserting a column!

Or for that matter just don't import the data you don't want. It really is very simple.

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.

Thanks very much for all the replies, I will try and see what I can do this evening.

Good luck and thanks again.


.

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.

Thanks again, especially Aye Robot. Your method was very simple, cheers.


.