Home 
Search 
Today's Posts 
#1




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. 121 would appear as 12, 94f as 2.25 and 92 as 4.5. 251 61jf 81 121 161 92 51 31f 94f 92 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 represented? 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 
#2




Help With Parsing Data
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. 121 would appear as 12, 94f as 2.25 and 92 as 4.5. 251 61jf 81 121 161 92 51 31f 94f 92 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 represented? 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 
#3




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. 121 would appear as 12, 94f as 2.25 and 92 as 4.5. 251 61jf 81 121 161 92 51 31f 94f 92 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 represented? 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 
#4




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




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




Help With Parsing Data
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) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Importing Data  Excel Worksheet Functions  
Parsing Data  Excel Discussion (Misc queries)  
Excel Macro to Copy & Paste  Excel Worksheet Functions  
The SEARCH function and parsing data  Excel Worksheet Functions  
Line Graph Data Recognition  Charts and Charting in Excel 