CALC: import csv/ covert text time to numeric time (duration)

[Morgan1]

Hello Folks
I’ve checked both:
Q 92382 and
Q 68968.
And both seem warm, but not warm enough.
I have text ‘0m 42s’ to be converted to numeric value for 0m 42s (00:42). How do I do it? Problems encountered: phantom single quotes (ticks) at the beginning of the resultant numeric formatting preventing the values behaving as numeric values; value of 42s shifting left to become 42m. See the my pics below for greater illumination:

0m 42s formatted as text, then

formatted as a number

and if as by magic, a single quote/ tick (’) is inserted at the beginning of the string to prevent it formatting as a number - see the edit field

this is what I want…

Q 92382 suggests that the tick is some sort of marker and not part of the cell content - but, it can be deleted with the (not) desired result:

now the 42 seconds has moved to 42 minutes…
Please, how do I get what I want?
Thanks

[EarnestAl]

It seems to me that the real problem is that the Hours are omitted when the time is shorter than 1 hour. A telephone conversation of over an hour is not at all uncommon but how is that done in the log? By increasing the minutes or by adding hours? I’m assuming hours. To match a time format it seems to need to include hours.

First test if there is an hour in the time by checking the length, otherwise add 00h at the beginning.

1. In cell G2 enter
   =IF(LEN(F2)>7,F2,CONCATENATE("00h ",F2)) and copy down.
2. Select and copy the cells in column
   G. Paste as unformatted text into
   column H and apply the formatting
   HH\h MM\m SS\s. The dreaded apostophe will appear.
3. Select the cells in column H and open Edit > Find and Replace (Ctrl+H)
4. In the Find field enter ^. In the Replace field enter & . Make sure Current Selection and Regular Expressions are both ticked
5. Click Replace All

This seems clunky to me; hopefully, someone else will have an elegant solution. Cheers, Al

[Lupp]

Sorry: Much too much image (number, size), much too little information.
May I assume the question:

I have durations given as texts like “15m 31s” supposed to mean “15 min 31s” (“m” is meaning the SI unit metre internationally), and I want to convert them to the standard representation of time values in Calc.

For such a text in cell A2 you can use:
=TIMEVALUE(REGEX(REGEX(IF(REGEX(A2;"h";1)=A2;"0h"&A2;A2);"h\D*|m\D*";":";"g");"s\D*$";""))
Generally time values in spreadsheets are actually made for TimeOfDay. To also be usable for calendaric DateTimeStamp they need to mandatorily use the unit day. Many disadvantages.

Since the REGEX() function is used, this requires LibreOffice V 6.2 or higher.
See attached example: (Attaching doesn’t work currently. Will later try again.)
Done 2020-11-18 16:50 UTC: ask277630durationsFromTextToNumbers.ods