Extracting Numeric Data from a Delimited Text String

[bwohlgemuth]

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

 

[John Michl]

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

 

[bwohlgemuth]

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.

 

[Harlan Grove]

(e-mail address removed) 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.

 

[bwohlgemuth]

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

 

[John Michl]

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