View previous topic :: View next topic 
Author 
Message 
bobajob General User
Joined: 27 Oct 2010 Posts: 7

Posted: Wed Oct 27, 2010 3:12 pm Post subject: "[Solved]"Extract alphanumeric to minutes and seco 


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("[^09]",A1,SEARCH("[09]",A1))1),SEARCH("[09]",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.
Last edited by bobajob on Thu Oct 28, 2010 1:50 pm; edited 1 time in total 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Wed Oct 27, 2010 7:28 pm Post subject: 


With..
3m 35.2s
in A1 and...
Code:  =VALUE(LEFT(A1;FIND("m";A1)1))/(24*60)+VALUE(TRIM(MID(A1;FIND("m";A1)+1;FIND("s";A1)FIND("m";A1)1)))/(24*60*60) 
in B1 with Number Format...
MM:SS.0
B1 returns
03:35.2
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


bobajob General User
Joined: 27 Oct 2010 Posts: 7

Posted: Thu Oct 28, 2010 1:55 pm Post subject: Extract alphanumeric to minutes and seconds format 


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! 

Back to top 


bobajob General User
Joined: 27 Oct 2010 Posts: 7

Posted: Fri Oct 29, 2010 11:00 am Post subject: Another Text extraction problem 


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. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Oct 29, 2010 1:19 pm Post subject: 


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 https://forum.openoffice.org 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri Oct 29, 2010 1:59 pm Post subject: 


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... Code:  =IF(ISNUMBER(FIND("m";A2));VALUE(LEFT(A2;FIND("m";A2)1));0) 
The furlongs is not too much harder because there are only 8 furlongs to the mile so we're only looking for one digit on the left side of the "f".
If there is an "f" it is the value of the character immediately to its left and if there isn't it is zero furlongs.
Again, with the string in A2 this is given by... Code:  =IF(ISNUMBER(FIND("f";A2));VALUE(MID(A2;FIND("f";A2)1;1));0) 
The yards is the difficult one.
If there is a "y" and there is an "f" then it is the value of the characters between them.
If there is a "y" and an "m" but no "f" then it is the value of the characters between the "y" and the "m".
If there is a "y" and only a "y" then it is the value of the characters from the start up to the character immediately to the left of the "y".
If there isn't a "y" then it is zero.
Again, with the string in A2 this is given by... Code:  =IF(ISNUMBER(FIND("y";A2));IF(ISNUMBER(FIND("f";A2));VALUE(MID(A2;FIND("f";A2)+1;FIND("y";A2)FIND("f";A2)1));IF(ISNUMBER(FIND("m";A2));VALUE(MID(A2;FIND("m";A2)+1;FIND("y";A2)FIND("m";A2)1));VALUE(LEFT(A2;FIND("y";A2)1))));0) 
The total yards, if miles is in B2, furlongs in C2 and yards in D2 is simply given by...
See attached doc
http://www.4shared.com/file/R1wseRiR/Miles_furlongs_yards_to_yards.html
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


bobajob General User
Joined: 27 Oct 2010 Posts: 7

Posted: Sat Oct 30, 2010 7:03 am Post subject: 


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 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
