Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 27th 05, 12:54 PM posted to microsoft.public.excel.misc
Saxman
 
Posts: n/a
Default 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

  #2   Report Post  
Old December 27th 05, 01:58 PM posted to microsoft.public.excel.misc
OZDOC1050
 
Posts: n/a
Default 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. 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



  #3   Report Post  
Old December 27th 05, 02:34 PM posted to microsoft.public.excel.misc
OZDOC1050
 
Posts: n/a
Default 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



  #4   Report Post  
Old December 27th 05, 03:33 PM posted to microsoft.public.excel.misc
Saxman
 
Posts: n/a
Default 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   Report Post  
Old December 27th 05, 03:36 PM posted to microsoft.public.excel.misc
Saxman
 
Posts: n/a
Default 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   Report Post  
Old December 27th 05, 03:39 PM posted to microsoft.public.excel.misc
Saxman
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Data Jillian Excel Worksheet Functions 9 December 23rd 05 01:45 PM
Parsing Data Saxman Excel Discussion (Misc queries) 4 December 8th 05 09:31 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
The SEARCH function and parsing data Michael Excel Worksheet Functions 2 September 19th 05 05:36 PM
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017