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" wrote in message
...
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,F IND("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,FIN D("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" wrote in message
...
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 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 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,F IND("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,FIN D("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:

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)