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