Help With Parsing Data

One of my hobbies is following horse racing and I need to parse some data.
Firstly, how could I clean up the following?

1/10
2/11
8/12
6/10
11/13
9/12
6/6
5/11
4/5
6/15

The above appear in cells H6:H15. I would like all the digits to the left
of '/' to be copied to K6:K15 and all the digits to the right of '/' to
appear in L6:L15.

Secondly, the following data appears in cells I6:I15. I would like to
divide the digit to the right of '-' into the digits to the left of '-'
with the result placed in cells M6:M15, i.e. 12-1 would appear as 12, 9-4f
as 2.25 and 9-2 as 4.5.

25-1
6-1jf
8-1
12-1
16-1
9-2
5-1
3-1f
9-4f
9-2

I also wondered if there was a better way of cleaning up the 'K's' in the
following data, without doing a find/replace? These could appear column N.

50K
20K
20K
22K
140K
14K
15K
25K
25K
18K

Finally, a bit of mathematics or lookup? In horse racing distances are
measured in furlongs (1 mile = 8 furlongs), 1m 2f = I mile, 2 furlongs (10
furlongs). Could the following data be cleaned up and re-presented?

1m 2f
1m 1f
1m 3f
1m 2f
2m
1m 3f
1m 3f
1m 5f
1m 6f
7f

Could the above be converted to:-
10
9
11
10
16
11
11
13
14
7

This horse racing business is a pain when it comes to presenting data.

TIA

part 1
in k6 place this and drag down
=LEFT(H6,FIND("/",H6,1)-1)
in l6 place this and drag down
=RIGHT(H6,LEN(H6)-FIND("/",H6,1))

May be other ways this is just 1
Pete

--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
"Saxman" <[email protected]> wrote in message
news:[email protected]...
> One of my hobbies is following horse racing and I need to parse some data.
> Firstly, how could I clean up the following?
>
> 1/10
> 2/11
> 8/12
> 6/10
> 11/13
> 9/12
> 6/6
> 5/11
> 4/5
> 6/15
>
> The above appear in cells H6:H15. I would like all the digits to the left
> of '/' to be copied to K6:K15 and all the digits to the right of '/' to
> appear in L6:L15.
>
> Secondly, the following data appears in cells I6:I15. I would like to
> divide the digit to the right of '-' into the digits to the left of '-'
> with the result placed in cells M6:M15, i.e. 12-1 would appear as 12, 9-4f
> as 2.25 and 9-2 as 4.5.
>
> 25-1
> 6-1jf
> 8-1
> 12-1
> 16-1
> 9-2
> 5-1
> 3-1f
> 9-4f
> 9-2
>
> I also wondered if there was a better way of cleaning up the 'K's' in the
> following data, without doing a find/replace? These could appear column
> N.
>
> 50K
> 20K
> 20K
> 22K
> 140K
> 14K
> 15K
> 25K
> 25K
> 18K
>
> Finally, a bit of mathematics or lookup? In horse racing distances are
> measured in furlongs (1 mile = 8 furlongs), 1m 2f = I mile, 2 furlongs (10
> furlongs). Could the following data be cleaned up and re-presented?
>
> 1m 2f
> 1m 1f
> 1m 3f
> 1m 2f
> 2m
> 1m 3f
> 1m 3f
> 1m 5f
> 1m 6f
> 7f
>
> Could the above be converted to:-
> 10
> 9
> 11
> 10
> 16
> 11
> 11
> 13
> 14
> 7
>
> This horse racing business is a pain when it comes to presenting data.
>
> TIA

part 4
im sure this could be done better

source info in cells H22:H31

formula in cells L22:L31 drag and adjust to suit ( as above sure there is a
better way )

=IF(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,0,IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)=0,0,LEFT(H22,IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,0,IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)))*8+IF(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,H22,RIGHT(H22,LEN(H22)-IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1))="",0,LEFT(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,H22,RIGHT(H22,LEN(H22)-IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)),1))

drag down

--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
"Saxman" <[email protected]> wrote in message
news:[email protected]...
> One of my hobbies is following horse racing and I need to parse some data.
> Firstly, how could I clean up the following?
>
> 1/10
> 2/11
> 8/12
> 6/10
> 11/13
> 9/12
> 6/6
> 5/11
> 4/5
> 6/15
>
> The above appear in cells H6:H15. I would like all the digits to the left
> of '/' to be copied to K6:K15 and all the digits to the right of '/' to
> appear in L6:L15.
>
> Secondly, the following data appears in cells I6:I15. I would like to
> divide the digit to the right of '-' into the digits to the left of '-'
> with the result placed in cells M6:M15, i.e. 12-1 would appear as 12, 9-4f
> as 2.25 and 9-2 as 4.5.
>
> 25-1
> 6-1jf
> 8-1
> 12-1
> 16-1
> 9-2
> 5-1
> 3-1f
> 9-4f
> 9-2
>
> I also wondered if there was a better way of cleaning up the 'K's' in the
> following data, without doing a find/replace? These could appear column
> N.
>
> 50K
> 20K
> 20K
> 22K
> 140K
> 14K
> 15K
> 25K
> 25K
> 18K
>
> Finally, a bit of mathematics or lookup? In horse racing distances are
> measured in furlongs (1 mile = 8 furlongs), 1m 2f = I mile, 2 furlongs (10
> furlongs). Could the following data be cleaned up and re-presented?
>
> 1m 2f
> 1m 1f
> 1m 3f
> 1m 2f
> 2m
> 1m 3f
> 1m 3f
> 1m 5f
> 1m 6f
> 7f
>
> Could the above be converted to:-
> 10
> 9
> 11
> 10
> 16
> 11
> 11
> 13
> 14
> 7
>
> This horse racing business is a pain when it comes to presenting data.
>
> TIA

On Wed, 28 Dec 2005 00:04:58 +1030, OZDOC1050 wrote:

> =IF(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,0,IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)=0,0,LEFT(H22,IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,0,IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)))*8+IF(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,H22,RIGHT(H22,LEN(H22)-IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1))="",0,LEFT(IF(IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))=0,H22,RIGHT(H22,LEN(H22)-IF(ISERROR(FIND("m",H22,1)),0,FIND("m",H22,1))-1)),1))

This works fine except when the value is 2m. 2m 1f, 2m 5f etc. are just
fine.

I much appreciate your efforts and input. I could never have worked this
out myself! 2 bets and 1 win today! This is keeping me amused on a snowy
day in the East of England!

On Tue, 27 Dec 2005 23:28:58 +1030, OZDOC1050 wrote:

> part 1
> in k6 place this and drag down
> =LEFT(H6,FIND("/",H6,1)-1)
> in l6 place this and drag down
> =RIGHT(H6,LEN(H6)-FIND("/",H6,1))

This works fine!

Thanks!

On Tue, 27 Dec 2005 23:28:58 +1030, OZDOC1050 wrote:

>> I also wondered if there was a better way of cleaning up the 'K's' in the
>> following data, without doing a find/replace? These could appear column
>> N.
>>
>> 50K
>> 20K
>> 20K
>> 22K
>> 140K
>> 14K
>> 15K
>> 25K
>> 25K
>> 18K

I have sorted this with the help of the first formula!

=LEFT(F6,FIND("K",F6,1)-1)