Extracting Numeric Data from a Delimited Text String

  • Thread starter Thread starter bwohlgemuth
  • Start date Start date
B

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
 
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.
 
(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.
Click to expand...
....

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.
 
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
 
Back
Top