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?

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

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

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

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.

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?

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