Remember Me?

#1
December 27th 05, 11:54 AM posted to microsoft.public.excel.misc
 Saxman Posts: n/a
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
December 27th 05, 12:58 PM posted to microsoft.public.excel.misc
 OZDOC1050 Posts: n/a
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
December 27th 05, 01:34 PM posted to microsoft.public.excel.misc
 OZDOC1050 Posts: n/a
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
December 27th 05, 02:33 PM posted to microsoft.public.excel.misc
 Saxman Posts: n/a
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
December 27th 05, 02:36 PM posted to microsoft.public.excel.misc
 Saxman Posts: n/a
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
December 27th 05, 02:39 PM posted to microsoft.public.excel.misc
 Saxman Posts: n/a
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)

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Jillian Excel Worksheet Functions 9 December 23rd 05 12:45 PM Saxman Excel Discussion (Misc queries) 4 December 8th 05 08:31 AM [email protected] Excel Worksheet Functions 0 December 1st 05 01:56 PM Michael Excel Worksheet Functions 2 September 19th 05 05:36 PM Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM

All times are GMT +1. The time now is 04:32 PM.