Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort or Filter to discard unnecessary data
I receive a spreadsheet containing data that I then need to filter to find
certain information and then populate yet another spreadsheet. I can speed up the process if I could just cut and paste the data but they are in different formats. Examples of the raw data is as follows: 1378:20:00, 958:20:00, 18:20, 1:40, 0:00 The maximum is 4 digits to the left of the first colon and 2 more sections divided by another colon as seen in 1378:20:00. The minimum will be the 0:00. Is there a function or formula to convert the above numbers to: 1378.20, 958.20, 18.20, 1.40 and 0.00??? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort or Filter to discard unnecessary data
Hi,
I'm assuming that you just want a formula that you can use in a cell. In which case, if you have your data in cell A1 you can use the following (it is a bit of a long winded formula, which can probably be written more concisely, but it should work as long as the maximum number of colons you have is 2): =IF(ISERROR(FIND(":",A1,FIND(":",A1)+1)),SUBSTITUT E(A1,":","."),SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND( ":",A1)+1)-1),":",".")) Hope that helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "rylv5050" wrote: I receive a spreadsheet containing data that I then need to filter to find certain information and then populate yet another spreadsheet. I can speed up the process if I could just cut and paste the data but they are in different formats. Examples of the raw data is as follows: 1378:20:00, 958:20:00, 18:20, 1:40, 0:00 The maximum is 4 digits to the left of the first colon and 2 more sections divided by another colon as seen in 1378:20:00. The minimum will be the 0:00. Is there a function or formula to convert the above numbers to: 1378.20, 958.20, 18.20, 1.40 and 0.00??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Discard Repeated Data? | Excel Discussion (Misc queries) | |||
Excel changes formula after data sort/filter, please help. | Excel Discussion (Misc queries) | |||
Is it possible to sort or filter data by color of the field? | Excel Worksheet Functions | |||
data sort/filter | Excel Worksheet Functions | |||
TRYING TO SORT OR FILTER DUPLICATE DATA | Excel Discussion (Misc queries) |