Race Distance Formula

[Saxman]

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?

 

[Ken Johnson]

Hi Saxman,

this seems to work

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

Ken Johnson

 

[Ken Johnson]

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

 

[Ken Johnson]

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

 

[Saxman]

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.

 

[Saxman]

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?

 

[Ken Johnson]

Hi Saxman,

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

 

[Guest]

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

 

[Ken Johnson]

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,FIND("m",A1)-1)*8+VALUE(MID(TRIM(A1),LEN(TRIM(A1))-1,1))))

Ken Johnson

 

[Saxman]

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,FIND("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.

 

[Ken Johnson]

You're welcome Saxman.

Thanks for the feedback.

Ken Johnson