OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

"[Solved]"Extract alphanumeric to minutes and seco

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bobajob
General User
General User


Joined: 27 Oct 2010
Posts: 7

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

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. Rolling Eyes


Last edited by bobajob on Thu Oct 28, 2010 1:50 pm; edited 1 time in total
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Oct 27, 2010 7:28 pm    Post subject: Reply with quote

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
View user's profile Send private message
bobajob
General User
General User


Joined: 27 Oct 2010
Posts: 7

PostPosted: Thu Oct 28, 2010 1:55 pm    Post subject: Extract alphanumeric to minutes and seconds format Reply with quote

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
View user's profile Send private message
bobajob
General User
General User


Joined: 27 Oct 2010
Posts: 7

PostPosted: Fri Oct 29, 2010 11:00 am    Post subject: Another Text extraction problem Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Oct 29, 2010 1:19 pm    Post subject: Reply with quote

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
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri Oct 29, 2010 1:59 pm    Post subject: Reply with quote

Hi bobajob,
Nice to see you're still alive Very Happy
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...
Code:
=B2*1760+C2*220+D2

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
View user's profile Send private message
bobajob
General User
General User


Joined: 27 Oct 2010
Posts: 7

PostPosted: Sat Oct 30, 2010 7:03 am    Post subject: Reply with quote

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)
Very Happy Rolling Eyes
Many thanks again Bob Fraser
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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