Help With Parsing Data
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
|