General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
When I copy trainer names into excel from the racecard are often followed by a number with no spaces in between. Is there a simple function that would show just the trainer name in another cell with the number removed.
ie
John Quinn50 to go to John Quinn
George Baker25 to go to George Baker
Many thanks!
ie
John Quinn50 to go to John Quinn
George Baker25 to go to George Baker
Many thanks!
Show More
rayzor,
At the moment I think the best way would be to use the LEN,Left functions below
=LEFT(A1, LEN(A1)-2)
So if you had in cell A1 Jones55, and cell A2 Smith7
Put the formula in the cells and they will remove the last two text entry, so you would be left with
Jones
Smith.
I am sure there are better ways but I cannot think of one just now, also this solution would need modifying if there was only a single number (e.g. 5 not 55)
At the moment I think the best way would be to use the LEN,Left functions below
=LEFT(A1, LEN(A1)-2)
So if you had in cell A1 Jones55, and cell A2 Smith7
Put the formula in the cells and they will remove the last two text entry, so you would be left with
Jones
Smith.
I am sure there are better ways but I cannot think of one just now, also this solution would need modifying if there was only a single number (e.g. 5 not 55)
If you've got your trainer data in cell A1 try this......
=IF(A1**"",--RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{".",0,1,2,3,4,5,6,7,8,9},""))))),"")
Seems to be a problem on here posting the 'less than' and 'greater than' symbols so I've represented them with red asterisks.
Please note that should the trainers name have only a zero or no digits at the end, the result of the formula will be 0.
=IF(A1**"",--RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{".",0,1,2,3,4,5,6,7,8,9},""))))),"")
Seems to be a problem on here posting the 'less than' and 'greater than' symbols so I've represented them with red asterisks.
Please note that should the trainers name have only a zero or no digits at the end, the result of the formula will be 0.
Another formula to cover 1 or 2 numbers at the end of trainers name ~
=IF(ISNUMBER(RIGHT(B4,2)*1),LEFT(B4,LEN(B4)-2),LEFT(B4,LEN(B4)-1))
=IF(ISNUMBER(RIGHT(B4,2)*1),LEFT(B4,LEN(B4)-2),LEFT(B4,LEN(B4)-1))
My last post is if ~
John Quinn50 is in cell B4
You can change the B4's in the formula to the cell John Quinn50 is in.
John Quinn50 is in cell B4
You can change the B4's in the formula to the cell John Quinn50 is in.
Can you use the the LTrim and RTrim functions to remove trailing or leading spaces in excel? If so, there's no need to guess how many spaces there are.
http://www.techonthenet.com/excel/formulas/index_alpha.php
http://www.techonthenet.com/access/functions/
...
http://www.techonthenet.com/excel/formulas/index_alpha.php
http://www.techonthenet.com/access/functions/
...
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539