Extrac numbers from cell

[Sebbebond]

Hi!
I'm doing a fun calculation and trying to find out how many hours me and my girlfriend spent on skype. Can you please help me?

The information I have in excel looks like this, all in A column.
[TABLE="width: 579"]
<colgroup><col></colgroup><tbody>[TR]
[TD][2011-12-20 18:44:06] name name: Call ended21 minutes 38 seconds
[/TD]
[/TR]
[TR]
[TD][2012-01-19 22:29:49] name name: Call started, 1 hour 7 minutes 51 seconds
[/TD]
[/TR]
</tbody>[/TABLE]

In best of all world I would get this in the B column:
22:38
1:07:51

But also fine with:
21 minutes 38 seconds
1 hour 7 minutes 51 seconds

Can you please help me?

Best regards
Sebastian

 

[strive4peace]

Hi Sebastianm

for date:
=MID(A2,2,10)

for time:
=MID(A2,13,8)

for duration:

Function GetDuration(pStr As String) As String
'121120 Crystal
   '[2012-01-19 22:29:49] name name: Call started, 1 hour 7 minutes 51 seconds
   
   Dim nPos As Integer
   nPos = InStr(22, pStr, ":")
   If nPos > 0 Then
      nPos = InStr(nPos + 1, pStr, ",")
      If nPos > 0 Then
         GetDuration = Mid(pStr, nPos + 1)
      End If
   End If
   
End Function

well you can parse it out more ... looks for the words "hour", "minute", "second" -- and add 1 to account for the possible "s" on end -- there is a space after anyway so that wont mess anything up

in a cell, you would then do this:
= GetDuration(A2)

 

[Sebbebond]

Hi!
Thanks for the reply but it doesnt seem to work, nothing happens
I use mac but I don't think it should affect the code beacuse it's normally works.

Do you have any suggestion?

Thank you!

Best regards
Sebastian

Hi Sebastianm

for date:
=MID(A2,2,10)

for time:
=MID(A2,13,8)

for duration:

Function GetDuration(pStr As String) As String
'121120 Crystal
   '[2012-01-19 22:29:49] name name: Call started, 1 hour 7 minutes 51 seconds
   
   Dim nPos As Integer
   nPos = InStr(22, pStr, ":")
   If nPos > 0 Then
      nPos = InStr(nPos + 1, pStr, ",")
      If nPos > 0 Then
         GetDuration = Mid(pStr, nPos + 1)
      End If
   End If
   
End Function

well you can parse it out more ... looks for the words "hour", "minute", "second" -- and add 1 to account for the possible "s" on end -- there is a space after anyway so that wont mess anything up

in a cell, you would then do this:
= GetDuration(A2)

 

[strive4peace]

Hi Sebastian,

put this code into a standard module:

Function GetDuration(pStr As String) As String
'121120 Crystal
   '[2012-01-19 22:29:49] name name: Call started, 1 hour 7 minutes 51 seconds
   
   Dim nPos As Integer
   nPos = InStr(22, pStr, ":")
   If nPos > 0 Then
      nPos = InStr(nPos + 1, pStr, ",")
      If nPos > 0 Then
         GetDuration = Mid(pStr, nPos + 1)
      End If
   End If
   
End Function

*** How to Create a Standard (General) Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

OR

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any application; procedures that are specific to a particular application; procedures for doing something specific; etc

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit 'require variable declaration

~~~

once the code compiles and the function is available for you to use, in a cell where you want to get "1 hour 7 minutes 51 seconds" for instance, use this formula:

= GetDuration(A2)

WHERE
A2 contains text such as
[2012-01-19 22:29:49] name name: Call started, 1 hour 7 minutes 51 seconds

It should work ... I tested it I would be surprised if using a mac affects a function such as this ... be sure to compile

</g>