How do I get numbers before and after a colon within a cell in Calc?

Posted by Dave M G on Super User See other posts from Super User or by Dave M G
Published on 2012-04-10T03:52:13Z Indexed on 2012/04/10 5:33 UTC
Read the original article Hit count: 636

Filed under:

openoffice-calc

|

libreoffice-calc

I have some cells which contain times entered in by users, which are written in the following format: mm:ss. There are never any hours. The seconds always have leading zeroes. The minutes may or may not have leading zeros.

Because I need to separate out the minutes and seconds for various operations, I need to be able to get at the numbers before and after the colon.

How do I select the number before a colon in a cell, and how do I select the number after?

© Super User or respective owner

Related posts about openoffice-calc

Ubuntu: Convert OpenOffice Calc to Excel workbook using CLI

as seen on Super User - Search for 'Super User'
I need to create an automated report in a spreadsheet format. Unfortunately, There seems to be an easy way to create these reports using OpenOffice Calc, but upper managements wants them in MS Excel format. As these reports are to be created and emailed automatically, is there a nice, command-line… >>> More
OpenOffice Calc: inserting image into a cell

as seen on Super User - Search for 'Super User'
I'm an Open Office Calc user. Is there a way to insert an image inside a cell? When I choose Insert - Picture it inserts it so it's floating over the cells, and that's now what I want. I want to be able to insert the image into cell A1, for instance. >>> More
Read data from separate CSV file in OpenOffice Calc

as seen on Super User - Search for 'Super User'
Hi, I have a very large CSV file (many thousands of rows) that I want to work with. Due to the size of the file, I don't want to import it into openoffice. Rather, I want to create a spreadsheet that contains formulas & graphs that read from this (or any other) CSV file I point it it. Ideally… >>> More
How to apply an Openoffice Calc template?

as seen on Super User - Search for 'Super User'
How I can easily apply a template to an existing OO Calc file? AFAIK it is possible only to make a new file from a template, copy the sheets in it, and save/overwrite the old file. But I don't like this way; there is a easier/better way to do this? Like a macro, or something like that. I just need… >>> More
Using a Script to automate data entry to an OpenOffice Calc Spreadsheet

as seen on Stack Overflow - Search for 'Stack Overflow'
I would like to be able to automate creating spreadsheets and adding data to an OpenOffice spreadsheet using a script (any scripting language is acceptable - PowerShell, Python, or even JavaScript). What is a suitable library or tutorial? >>> More

Related posts about libreoffice-calc

Changing the cell range of conditional formatting in LibreOffice Calc

as seen on Super User - Search for 'Super User'
Is there any way of changing the range of conditional formatting in LibreOffice Calc (version 3.6)? I have a set applied already to a range of cells, but there is no obvious way of changing which cells a given set of conditional formatting is applied to. I either have to copy and paste the formatting… >>> More
LibreOffice Calc SEARCH and FIND functions

as seen on Super User - Search for 'Super User'
I am trying to process some data in Calc. One of the steps involve finding if a certain string is part of one of the column. I tried using FIND and SEARCH functions. Both behave in the same way and I am not getting correct results. E.g. Say I have following strings in Column A NY SF LON CAN US and… >>> More
Flexible argument list in LibreOffice Calc Macro

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to write a function that geometrically links performance data which is usually provided as percentages, so the function will basically return (1+a)*(1+b)*(1+c)* … *(1+x)-1 This should be done using LibreOffice-calc and it should behave similarly to the regular sum function. As you may throw… >>> More
Change the icon color in LibreOffice Calc

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
There appears to be no way of changing icon color in a calc chart. After creating an XY scatter plot, I can select "format data series" and can change the icon symbol and size, but not the color. There is an drop-down for line color, but when I do not have lines displayed this drop-down menu is grayed… >>> More
System crash when trying to drag cells in LibreOffice Calc

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
Some time after upgrady to Saucy I noticed several freezes and crashes when simply trying to drag content of selected cells to another place in LibreOffice Calc (version 4.1.2.3 from repository). The system stops responding - although for example Clementine still plays music from playlist - I am… >>> More