Removing Ranking Numbers with Text Functions

[mrvp]

Hi,

I'm looking for some advice on how to remove ranking numbers from Sports teams in a set of data. I've re-worked the set of data (it was originally set up as a winner, winners score, at (if they weren't at home),loser, losers score - which I re-arranged with an IF function using the "at" - so that it's home vs away).

The problem that I have is, to do some straight analysis (which was why I was keen to have the home and away setup) I will be looking to use a VLookup table and IF functions. The issue I have is that the Sports results are over 13 weeks and they have ranking figures in (e.g.):

(4) Oregon
(7) Oregon
(9) Oregon
(12) Oregon

Some however aren't ranked at all.

Therefore I'm thinking to strip all of the ranking numbers out (so the VLookup or IF just picks up Oregon and doesn't treat it as 4 different teams) I can't use LEFT or RIGHT because of the different length of the Team Names (e.g. West Virginia) and the fact that some teams are not ranked (e.g. Kent State) so the length of the name isn't consistent.

I'm thinking that I could do this manually but don't particularly fancy it as I'm looking at about 900 lines (with two teams per line) so hence am keen to try and find a formula way of doing this.

I would be grateful for any advice that anyone may have on this.

Many thanks

[martindwilson]

with your values starting a1
=IF(ISNUMBER(SEARCH(")",A1)),TRIM(MID(A1,SEARCH(")",A1)+1,2

[martindwilson]

with your values starting a1
=IF(ISNUMBER(SEARCH(")",A1)),TRIM(MID(A1,SEARCH(")",A1)+1,2

[mrvp]

Hi,

This was almost spot on. Excel corrected the formula to this:

=IF(ISNUMBER(SEARCH(")",D2)),TRIM(MID(D2,SEARCH(")",D2)+1,2)))

With D2 being the cell with the first of my home teams in it. The formula worked in principle with anything without a ranking number returning a "FALSE" and anything with a ranking number returning the first letter of the team (e.g. O in the case of Oregon). I can see that if I can get it to return the full team I could just set up a second dummy column to put the table together using an IF function (e.g. IF it equals FALSE, return the original cell, or return the new cell - I can see it working out but just haven't thought it through yet).

I can't quite see what's missing in the formula above that it is only returning the first letter after chopping the ranking number out. The formula works fine for cutting the number out (e.g. it cut out either a (4) or a (14)) - it just returns only the first letter though.

Any advice would be greatly appreciated.