Sort or Filter to discard unnecessary data

[rylv5050]

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???

 

[SeanC UK]

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)),SUBSTITUTE(A1,":","."),SUBSTITUTE(LEFT(A1,FIND(":",A1,FIND(":",A1)+1)-1),":","."))

Hope that helps,

Sean.