help with string manipulation

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

Hi all

Firstly apolgies if my original post under a similar heading came through
- I could not find it on goofle two days after I posted so I presume it
has gone AWOL

I have a column which contains values exported by some thirdparty software

sample data below
5m 38s
21h 21m 06s
2m 58s
33m 26s
33m 30s
2m 16s
27m 39s
37s
11m 10s
11m 21s

This a a time duration which I need to convert to the number of seconds

I have come up with this clunker of a formula which works - but I feel
there must be a better way

IF(ISERROR(FIND("h",E3)),0,LEFT(E3,FIND("h",E3)-1)*3600)
+IF(ISERROR(FIND("m",E3)),0,SUBSTITUTE(RIGHT(LEFT(E3,FIND("m",E3)),3),"m",""))
*60+VALUE(LEFT(RIGHT(E3,3),2))+24

I have added line breaks so it posts properly!

I just know there must be a simpler neater way of doing this.

Thanks

Trev
 
Hi
I would try to convert your numbers into real Excel times first if this
is a possible solution for you
 
if you had hours, minutes, seconds for each entry, you could do a few
edit|replaces
h(space) with :
m(space) with :
s(no space) with (nothing)

And excel would see it as time.

You could do the same kind of thing with this formula (yech):

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("h",A1)),"0h ","")
&IF(ISERROR(FIND("m",A1)),"0m ","")&TRIM(A1),"h ",":"),"m ",":"),"s",""))

(Format as time)

It worked with your test data. But it'll blow up real good if your data has
anything like:

21h 06s
 
Hi
I would try to convert your numbers into real Excel times first if this is
a possible solution for you
Click to expand...
The data comes striaght from another program and I do not believ I can
manipulate its format. It exports the data to a csv file. As I said that
frmula does the trick for me I was just hoping for something more concise
and neater.

Thanks

Trev
 
Hi Trevor
o.k. then you may save some function calls in your function. Try
=IF(ISERROR(FIND("h",E3)),0,LEFT(E3,FIND("h",E3)-1)*3600)+IF(ISERROR(FI
ND("m",E3)),0,MID(E3,FIND("m",E3")-2,2))*60+MID(E3,LEN(E3)-2,2)+24
 
if you had hours, minutes, seconds for each entry, you could do a few
edit|replaces
h(space) with :
m(space) with :
s(no space) with (nothing)

And excel would see it as time.

You could do the same kind of thing with this formula (yech):

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("h",A1)),"0h
","")
&IF(ISERROR(FIND("m",A1)),"0m ","")&TRIM(A1),"h ",":"),"m
",":"),"s",""))

(Format as time)

It worked with your test data. But it'll blow up real good if your data
has anything like:

21h 06s
Click to expand...

Thanks Dave for your suggestion

Actually it only worked for the data that had h m and s
I actually quite like your approach though and it will be easy enough for
me to add 0h and 0m where needed just by evaluating the length. I quite
like the fact that I get the reults in Excel time format.


Trev\
 
You have a formula that works for you. That worksheet formula will evaluate
quicker than a userdefined function.

But sometimes when formulas get too long, I'll use a UDF.

Here's one that seems to work ok:

Option Explicit
Function Conv2Time(myStr As String) As Variant

Dim myArr As Variant
Dim iCtr As Long
Dim myHour As Long
Dim myMin As Long
Dim mySec As Long

myArr = Split97(Trim(myStr), " ")

myHour = 0
myMin = 0
mySec = 0

For iCtr = LBound(myArr) To UBound(myArr)
If StrComp(Right(myArr(iCtr), 1), "h", vbTextCompare) = 0 Then
myHour = Val(myArr(iCtr))
ElseIf StrComp(Right(myArr(iCtr), 1), "m", vbTextCompare) = 0 Then
myMin = Val(myArr(iCtr))
ElseIf StrComp(Right(myArr(iCtr), 1), "s", vbTextCompare) = 0 Then
mySec = Val(myArr(iCtr))
Else
Conv2Time = CVErr(xlErrRef)
Exit Function
End If
Next iCtr

Conv2Time = TimeSerial(myHour, myMin, mySec)

End Function

'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

So if you had your string in A1, you could put =conv2time(a1) in another
adjacent cell.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you're using xl2k or higher, you can remove the split97 function and
change this line:
myArr = Split97(Trim(myStr), " ")
to
myArr = Split(Trim(myStr), " ")

(Split was added in xl2k)
 
Hi Trevor
o.k. then you may save some function calls in your function. Try
=IF(ISERROR(FIND("h",E3)),0,LEFT(E3,FIND("h",E3)-1)*3600)+IF(ISERROR(FI
ND("m",E3)),0,MID(E3,FIND("m",E3")-2,2))*60+MID(E3,LEN(E3)-2,2)+24 ----------------------------------^
Click to expand...
Thanks Frank - a neater formula than mine but it did not work in all
cases as the minutes section is not padded with a zero nut the seconds
part is!!!!! My data subset did not include enough examples.

Also a gremlin must have crept in to your post as there should not be a "
as marked in above (use a mono spaced font)!!!

Thanks for your time and to Dave as well. My formula actually works for
all my data its just a bit clunky. Well it works for all the output I
have - It might well run across an exception or at some point though!! But
I have learned a thing or two and that is that there is often not any
correct way to do something.

Incidentally the output is from the accounting part of the kppp dialer for
Linux and as I said before I do not think I can format the output.

Its really nice to post to a group like this where people go out of there
way to help others - I guess my problem could also have been a challenge
to do it simpler faster better!

Thanks again

Trev
 
You have a formula that works for you. That worksheet formula will
evaluate quicker than a userdefined function.

But sometimes when formulas get too long, I'll use a UDF.

Here's one that seems to work ok:
Click to expand...
<UDF snipped>

Wow thanks Dave

Yet another approach - I would not have thought of doing that as I most of
the macros i have made have been using the recorder.

However I have done a few fucntions and routines in MSACCESS so I am
familiar with the process and language.

I am actually using OpenOffice and Linux but I have Access2k and Windows2k
(Dual Boot) So I should be able to convert the macro

Thanks again I will play with this one a bit

Trev
 
Back
Top