Extracting Numeric Data from a Delimited Text String

Difficulty - No VBA Preferably

I'm trying to pull numeric data from an imported CSV file. The data I
am trying to pull is in a DDD:HH:MM:SS format. I've already written a
function that pulls the data from those fixed fields...

=IF(A2=0,0,SUM(SUM(24*(VALUE(LEFT(A2,3))))+(VALUE(MID(A2,5,2)))+(SUM(VALUE(MID(A2,8,2)))/60)))

What the function does is convert the DDD:HH:MM into a decimal form
which is easier to read and to perform additional functions with.

Here's the problem....most of the data comes into the exported csv file
like this...

000:11:05:46 ----> function ----> 11.08 CORRECT!

But on occasion, it exports like this....

00:11:05:46 ----> 0.05 ---- NOT CORRECT!

The problem with my function is that is the date field is shortened, it
screws up the answer completely. What I need is a way to pull the
numerical data out of the field by using the ":" instead of just using
fixed characters. I've obviously rewritten the formula in those
specific cases, but I was looking for something more elegant if it
exists. Also, I really don't want to use "Text to Columns" since this
worksheet will eventually be used by a bunch of people and I'm trying
to make it as user friendly as possible.

Thanks,
Brian

Do you need the date information or just time? If just time, you could
use:

=TIMEVALUE(RIGHT(A2,8))*24 which gives an answer of 11.096 (I'm not
sure where your 11.08 is coming from)

If you need the date info, extract it with the following:
=LEFT(A2,FIND(":",A2)-1) .

That should get you started.

- John

Actually, it doesn't quite work that well. Here's the problem

001:02:59:48 2.996666667
000:11:05:46 11.09611111

The first one is wrong. The 001 is the number of days this event
occured, so in this case, the answer should be 26.996666667.

I'm not really looking for a date export, more of a number export in
front of and between the ":" since they are a constant.

[email protected] wrote...
>Actually, it doesn't quite work that well. Here's the problem
>
>001:02:59:48 2.996666667
>000:11:05:46 11.09611111
>
>The first one is wrong. The 001 is the number of days this event
>occured, so in this case, the answer should be 26.996666667.
....

Try

=24*(LEFT(x,FIND(":",x)-1)+RIGHT(x,8))

where x is a reference to the cell in question. The result is hours
with fractional hours.

That did it, thanks again for all of your help!!!!!

Combine the two examples I gave and you should have your answer. Note
I multiplied the days number by 24 to get the number hours.

=TIMEVALUE(RIGHT(A4,8))*24+LEFT(A4,FIND(":",A4)-1)*24

- John