Race Distance Formula
 

I have a column A1:A17 which displays variations the following data.

5f
6f
7f
1m
1m 1f
1m 2f
1m 3f
1m 4f
1m 5f
1m 6f
1m 7f
2m
2m 1f
2m 2f
2m 3f
2m 4f
2m 5f

The data refers to race distances.

As 1m (mile) = 8f (furlongs), how can I simplify the data to show the
following data in B1:B17?

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

I was using the ISERROR function, but that does not work anymore, maybe
because of formatting?

Race Distance Formula
 

Hi Saxman,

this seems to work

=IF(ISERROR(FIND("f",I1)),VALUE(LEFT(I1,FIND("m",I 1)-1))*8,IF(ISERROR(FIND("m",I1)),VALUE(LEFT(I1,1)),V ALUE(LEFT(I1,FIND("m",I1)-1))*8
+VALUE( MID(I1,FIND("m",I1)+2,1))))

Ken Johnson

Race Distance Formula
 

This is a smidgeon shorter and also seems to work.

This time the miles and furlongs were in column A, starting in A2...

=IF(ISERROR(FIND("
",A2)),VALUE(LEFT(A2,LEN(A2)-1))*(8-7*--(RIGHT(A2,1)="f")),8*VALUE(LEFT(A2,FIND("m",A2)-1))+VALUE(MID(A2,FIND("
",A2)+1,1)))

The first part looks for the space between the "m" and the furlong
value, so there should be a space between the first and second speech
marks, ie FIND(" ",A2 etc. Similarly the last and second last speech
marks.

Ken Johnson

Race Distance Formula
 

I have a nasty habit of not fully reading the question, so try...

=IF(ISERROR(FIND("
",A1)),VALUE(LEFT(A1,LEN(A1)-1))*(8-7*--(RIGHT(A1,1)="f")),8*VALUE(LEFT(A1,FIND("m",A1)-1))+VALUE(MID(A1,FIND("
",A1)+1,1)))

in B1, making sure the space is between the first and second speech
marks, as well as the last and second last speech marks.

Ken Johnson

Race Distance Formula
 

Ken Johnson wrote:
I have a nasty habit of not fully reading the question, so try...

=IF(ISERROR(FIND("
",A1)),VALUE(LEFT(A1,LEN(A1)-1))*(8-7*--(RIGHT(A1,1)="f")),8*VALUE(LEFT(A1,FIND("m",A1)-1))+VALUE(MID(A1,FIND("
",A1)+1,1)))

That works a treat Ken and much shorter than my previous version.

Thanks very much.

Race Distance Formula
 

Ken Johnson wrote:
I have a nasty habit of not fully reading the question, so try...

=IF(ISERROR(FIND("
",A1)),VALUE(LEFT(A1,LEN(A1)-1))*(8-7*--(RIGHT(A1,1)="f")),8*VALUE(LEFT(A1,FIND("m",A1)-1))+VALUE(MID(A1,FIND("
",A1)+1,1)))

in B1, making sure the space is between the first and second speech
marks, as well as the last and second last speech marks.


I just realised why my old formula is not working.

The later is now presented without a space between the two sets of data
in each cell.

I guess I should omit the exclamation marks?

Race Distance Formula
 

Hi Saxman,
Saxman wrote:

I just realised why my old formula is not working.

The later is now presented without a space between the two sets of data
in each cell.

I guess I should omit the exclamation marks?

Are you referring to exclamation marks in your original formula?

I haven't seen your original formula so I can't comment.

The following formula does not rely on the space separating the miles
and furlongs so it will work equally well with spaced and unspaced
data...

=IF(OR(ISERROR(FIND("m",A1)),ISERROR(FIND("f",A1)) ),VALUE(LEFT(A1,LEN(A1)-1))*IF(RIGHT(A1,1)="m",8,1),VALUE(LEFT(A1,FIND("m" ,A1)-1)*8+VALUE(MID(A1,LEN(A1)-1,1))))

Ken Johnson

Race Distance Formula
 

nice job Ken...tell Mr. Sax not to place a blank space after "f"....

"Ken Johnson" wrote:

Hi Saxman,
Saxman wrote:

I just realised why my old formula is not working.

The later is now presented without a space between the two sets of data
in each cell.

I guess I should omit the exclamation marks?

Are you referring to exclamation marks in your original formula?

I haven't seen your original formula so I can't comment.

The following formula does not rely on the space separating the miles
and furlongs so it will work equally well with spaced and unspaced
data...

=IF(OR(ISERROR(FIND("m",A1)),ISERROR(FIND("f",A1)) ),VALUE(LEFT(A1,LEN(A1)-1))*IF(RIGHT(A1,1)="m",8,1),VALUE(LEFT(A1,FIND("m" ,A1)-1)*8+VALUE(MID(A1,LEN(A1)-1,1))))

Ken Johnson

Race Distance Formula
 

4pinoy wrote:
nice job Ken...tell Mr. Sax not to place a blank space after "f"....


Hi 4pinoy,

Thanks for that.

This version seems to take care of any feral trailing spaces as well as
multiple spaces between the mile and furlong measures...

=IF(OR(ISERROR(FIND("m",A1)),ISERROR(FIND("f",A1)) ),VALUE(LEFT(A1,LEN(TRIM(A1))-1))*IF(RIGHT(TRIM(A1),1)="m",8,1),VALUE(LEFT(A1,FI ND("m",A1)-1)*8+VALUE(MID(TRIM(A1),LEN(TRIM(A1))-1,1))))

Ken Johnson

Race Distance Formula
 

Ken Johnson wrote:
4pinoy wrote:
nice job Ken...tell Mr. Sax not to place a blank space after "f"....


Hi 4pinoy,

Thanks for that.

This version seems to take care of any feral trailing spaces as well as
multiple spaces between the mile and furlong measures...

=IF(OR(ISERROR(FIND("m",A1)),ISERROR(FIND("f",A1)) ),VALUE(LEFT(A1,LEN(TRIM(A1))-1))*IF(RIGHT(TRIM(A1),1)="m",8,1),VALUE(LEFT(A1,FI ND("m",A1)-1)*8+VALUE(MID(TRIM(A1),LEN(TRIM(A1))-1,1))))

This works fine once I sorted the references out! I did go to a 70th
birthday party last night with free food and drink!

I am very grateful. I don't know how you do it!

Thanks.

Race Distance Formula
 

You're welcome Saxman.

Thanks for the feedback.

Ken Johnson