"[Solved]"Extract alphanumeric to minutes and seco

[bobajob]

I have a problem with Alphanumeric cells. I copy and paste pages from the web. When I paste them in there is no real problem until I want to calculate with one of the columns, which is expressed in the following format "3m 35.2s" (without the ""). With this column in A, I have tried VALUE(A1) which returns a 502 error. Reading through the posts I came across a short formula which suggested using LEFT in the formula. So I entered VALUE(LEFT(A1)) and this returns 00:00.00 which is the number format for minutes and seconds. Great! But when I want to copy and paste special column A to column B whith "numbers" only in the paste special dialogue box everything in column B disappears. I've tried TRIM I've tried Search and Replace, also text manipulation arrays etc..
MID(LEFT(A1,SEARCH("[^0-9]",A1,SEARCH("[0-9]",A1))-1),SEARCH("[0-9]",A1), 255)
gives me 3
I can change col A to 3m35.2s (i.e. without spaces) but I want it in a format that I can use to calculate.
I would really like to be able to change 3m 35.2s in col A to 03:35.2 in col B, therefore I'm looking for, maybe not a genius but at least someone with more savvy than I have (that wouldn't be difficult, I hear you say!) to guide me to Nirvana.

[ken johnson]

With..
3m 35.2s
in A1 and...

[bobajob]

Many thanks indeed for your very prompt reply. I struggled with this for a while, as I am 75 I was beginning to wonder if I would solve the problem before the Big Reaper came visiting!

[bobajob]

I have another problem for you. From the same sites I copy and paste a page to Calc.
I have in column A, text entries, much the same as the text entries for minutes and seconds as previously but this time they are in varying letters and numbers, e.g. "5f" (again Without the quotes)"7f39y", "1m", "1m2f", "1m2f110y" "2m110y".

As you have probably guessed these are race lengths in UK racing.

What I would like to do is convert these to yards, either in one operation "total yards" in one column or as four additional columns, "miles", "furlongs", "yards" "total yards", putting either a zero in the miles column and a zero in the furlong or yards column if it is a 6f race, for instance.

E.g.in the four column setup, for a 7f39y race we would get 0 in the miles column, 7 in the furlongs column and 39 in the yards column or for the 2m110y race you'd get 2 in col B 0 in Col C and 110 in col D. Col E would have 2559.

FYI (just in case you don't know, 1760y/mile, 220y/furlong, sorry to be so pedantic, just in case you are a km man.)
I've tried with IF statements eg IF(FIND("m",A4)="",0,VALUE(LEFT(A1,FIND("m",A1)-1))) in column B. But as soon as it comes to a cell contents such as 7f I get a #VALUE! message in the cell.

Unfortunately the answer is not obvious to me! But that's no big surprise. Many thanks in advance.

[Villeroy]

FIND returns a number which is the position number where the search value is found.
FIND("c";"abcdef") returns 3 since c is found on position 3.

Since decades all spreadsheets return #VALUE for
FIND("x";"abcdef") although error #N/A or position 0 would be more obvious.
IF(FIND("m",A4)="";0;...) will never return 0 because FIND will never return "" or any text. It's a number or #VALUE.

=ISNUMBER(FIND("m";A4))*VALUE("99")
returns 0 or 99 without the confusing IF clause.
ISNUMBER returns nothing but 1 or 0 and the multiplication with some number returns either that number or 0.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[ken johnson]

Hi bobajob,
Nice to see you're still alive
As Villeroy has already stated, you needed to use either the ISNUMBER or ISERROR function with the FIND function when testing for the presence of an "m", "f" or "y" in the string.
Getting the miles is reasonably easy since if there is an "m" it is the value of the characters from the start up to the character before the "m", and if there isn't an "m" it is zero miles.
With the string in A2 this is given by...

[bobajob]

Many thanks to both Villeroy and Ken Johnston, the answers are superb! It was only after sending my email that I realised that in the "Jumps" (or NH) section I have distances such as 3m2f110y(C)(19) and 2m4f110y(H)(10) but as stated as we are really only concerned with the digits to the left of the "y".
FYI the C in brackets means a Chase the 19 means the no. of fences, the h means an hurdle and the 10 means the no. of "flights" (brushwood fences). As you both have increased my knowledge I thought it only right to increase yours (in case you didn't know)

Many thanks again Bob Fraser