時間の表示をmm:ssで処理する

質問

A列のセルには文字と時間(mm:ss)が混入している文字列があります。
　　（mm:ssは、大文字の場合も小文字の場合もあります。）

45:19tomatoです。
45：19tomatoです。
只今、23:20をお知らせします。

このターゲット文字列から時間(mm:ss)の部分のみC列に抜き出したいので
以下のようなコードを作成してみましたが、
C列の結果は、　　
　mm:ssでは無く、hh:mm:ssで表示されてしまいます。

　　45：19　---＞　21：19：00
　　23：20　----＞ 23：20：00

これを、以下のように分に直したい状態で記入するにはどうすれば良いでしょうか　？
　　45：19　---＞　0：45：19
　　23：20　----＞ 0：23：20

Sub Find_Time(s, result)
'VBAの画面でツール(T)→参照設定(R)のダイアログを開き、
'「Microsoft VBScript Regular Expressions 5.5」にチェックを付けると
'正規表現のRegExpクラスが利用できるようになります。

Dim reg As New RegExp '正規表現クラスオブジェクト

'// 検索条件＝数字以外を抽出（大文字、小文字）
reg.Pattern = "[^0-9０-９:：]"

'// 文字列の最後まで検索する
reg.Global = True

'// 指定セルの数字以外の文字を空文字に置き換える
result = reg.Replace(s, "")
End Sub

Sub Extract_Time()
Dim result
Dim i As Integer

For i = 1 To 100
Call Find_Time(Sheets("整形").Cells(i, "A").Value, result)
Sheets("整形").Cells(i, "C") = result
Next i
End Sub

回答No.4

kkkkkm · ベストアンサー

* 0.0166666666666という方法は知りませんでした。やるとしたら時分で認識させてから掛けるのだと思いますからセルに入れて

Sheets("整形").Cells(i, "C") = result
Sheets("整形").Cells(i, "C") = Sheets("整形").Cells(i, "C") * 0.0166666666666

こんな感じでしょうか。24時間以内(3行目)だと
Sheets("整形").Cells(i, "C") = TimeValue(result) * 0.0166666666666
でいけました。

ただ、24時間以上でシリアル値で出すのでしたら掛け算をせずに
Sheets("整形").Cells(i, "C") = TimeSerial("00", Left(result, 2), Right(result, 2))
で出したい時分秒がセルに表示されます。ユーザー定義をh:mm:ssに変更しないと駄目でしたが。

他にも何かあるかもしれませんが、時間とか日付とかはややこしいので面倒です。

お礼

追加の情報ありがとうございます。

教えてもらった以下の4パターンで試してみました。
一度、セルに書き込んでから掛け算しないと思った処理が出来ないのですね。
　（全く思ってもいなかったので勉強になります。）

1 -----------------------------
Sub Extract_Time()
Dim result As Variant
Dim i As Integer

For i = 1 To 3
result = Find_Time(Sheets("整形").Cells(i, "A").Value)
Sheets("整形").Cells(i, "C") = Format("00:" & result, "h:n:s")
Next i
End Sub

2 -----------------
Sub Extract_Time()
Dim result As Variant
Dim i As Integer

For i = 1 To 3
result = Find_Time(Sheets("整形").Cells(i, "A").Value)
Sheets("整形").Cells(i, "C") = result
Sheets("整形").Cells(i, "C") = Sheets("整形").Cells(i, "C") * 0.0166666666666
Next i
End Sub

3 --------------------- 24時間までは、OK(24時間以上は、エラーが出ます）
Sub Extract_Time()
Dim result As Variant
Dim i As Integer

For i = 1 To 3
result = Find_Time(Sheets("整形").Cells(i, "A").Value)
'Sheets("整形").Cells(i, "C") = result
Sheets("整形").Cells(i, "C") = TimeValue(result) * 0.0166666666666
Next i
End Sub

4 --------------------------------
Sub Extract_Time()
Dim result As Variant
Dim i As Integer

For i = 1 To 3
result = Find_Time(Sheets("整形").Cells(i, "A").Value)
Sheets("整形").Cells(i, "C") = TimeSerial("00", Left(result, 2), Right(result, 2))
Next i
End Sub

-----------------------------------------------------

回答No.6

imogasi

質問点のよくわからない質問と思った。
すでに回答も出ているし議論もされているが、興味を持ったのでやってみた。
ーーー
例データ
A２:B６
A列（原文）　　B列時刻（時刻シリアル値）<==処理結果
45:19tomatoです。 45:19
45：19tomatoです。 45:19
只今、23:20をお知らせします。 23:20
22:19tomatoです。 22:19
それは１８：４５のことでした 18:45
ーー
WEBの記事を修正
Sub Sample2()
'正規表現を使用して文字列を検索する
'＊＊Executeメソッド＊＊

Dim Re As Object
Dim Mc As Object
Dim msg As String
Dim i As Long

'正規表現を利用する為のRegExpオブジェクトを作成
Set Re = CreateObject("VBScript.RegExp")
'=========
For j = 2 To 6
x = Cells(j, "A")
x = StrConv(x, vbNarrow)
MsgBox x
With Re
'正規表現パターンの設定
'[A-Z,a-z,0-9]で全てのアルファベットと0～9範囲の数値
'「+」で直前に指定した文字の繰り返しを表す
'[.][o][m]で「.com」という文字を表す
'この指定で「.com」で終わる文字列という意味になる
'.Pattern = "[A-Z,a-z,0-9]+[.][o][m]"
'.Pattern = "(^[1-5][0-9]|2[0-3])[:：][0-5][0-9]"
.Pattern = "([1-5][0-9])[:：][0-5][0-9]"
'複数マッチを有効
.Global = True

'セルA1に入力された文字列に対して検索を実行する
Set Mc = .Execute(x)
End With

With Mc
'対象文字の有無を判定
If .Count > 0 Then
For i = 0 To .Count - 1
'対象文字「有り」の場合、文字を取得
msg = msg & i + 1 & "番目の文字列：" & _
.Item(i).Value & vbLf
Cells(j, "B") = .Item(i).Value 'TimeValue(.Item(i).Value)
Cells(j, "B").NumberFormatLocal = "[h]:mm"
Next
Else
'対象文字「なし」の場合の文字列
msg = "該当なし"
End If
End With

'メッセージボックスの表示
If msg = "該当なし" Then
MsgBox msg, vbCritical
Else
MsgBox msg, vbInformation
End If
Next j
End Sub
・パターンを少し質問と変えたこと。質問にはデータの状況など説明がない。正規表現を使うような課題では、説明が不完全だろう。
・一旦StrConv(x, vbNarrow)を使って、半角に統一した。
・説明がないので、細かい点で独断でやっている点あり。
上記では正規表現の点を除いて、特にもんだいとは思わない。
文字列データから、時刻シリアル値に直すことなど、それほど疑問になることでは無かろう。
小生の質問の読みが、不完全かな？

回答No.5

kkkkkm

> 一度、セルに書き込んでから掛け算しないと思った処理が出来ないのですね。

今回のように時間のシリアル値に対しての掛け算などが必要な場合、セルに24時間を超えると思える時間の文字列(45:19のように形式としてデータが不足している{本来は45:19:00など})を入れてやるとセルの内容は自動で「エクセルの考える適切」なシリアル値として変換されますので、手短な方法として利用しました。ただ、あまりそのような事は無いと思います。ちなみにValだと先頭から数字じゃなくなるまでの部分だけが数値になります。

セルの日付や時刻のシリアル値が見たい場合、ご存じのように書式を標準にするか、たとえば
2 -----------------
で

Sheets("整形").Cells(i, "C") = result
の後に
Debug.Print "result = "; result; " : セルの実データ ="; Sheets("整形").Cells(i, "c").Value2
としたらresultの値と掛け算する前のセルの実際のデータ(シリアル値)がイミディエイトウィンドウに表示されます。

時間の場合.Value2でなくてもシリアル値を取り出せる感じですが、日付などは.Value2でないとシリアル値を取り出せないので今回は時間ですが一応.Value2にしています。

お礼

丁寧な解説をありがとうございます。

エラーが出たり思ったような結果が出ないと
　いつも行きあたりばったりの対策しかしないので
　　自分の処理能力を超えると思考停止してしましまう。

デバックの方法をもう少し本気で学ぶ必要あるのですが
　中々思い道理になりません。

アドバイスにより
　何とか今回も思ったような処理が完成しました。

協力ありがとうございます。

回答No.3

kkkkkm

これは全くの蛇足で大きなお世話で、個人的好みだろうとも思いますので独り言です。

resultを引数として渡さずに

Function Find_Time(s) As Variant
Dim reg As New RegExp

reg.Pattern = "[^0-9０-９:：]"
reg.Global = True
Find_Time = reg.Replace(s, "")
End Function

で、結果をresultに

result = Find_Time(Sheets("整形").Cells(i, "A").Value)

がしっくりくる気もします。

お礼

試行錯誤の最中、
　kkkkkmさん、今回もレスいただき感謝します。

関数の方がしっくりくる事との事で以下のようにコード変更してみました。

数値に直して*0.0166666666666で思ったようにmm:ssになるだろうと思いましたが
上手くいきませんでした。

教えてもらった
Sheets("整形").Cells(i, "C") = Format("00:" & result, "h:n:s")
に変更して表示は、mm:ssに上手く変更されました。

------------------------------------------

Function Find_Time(s) As Variant
'VBAの画面でツール(T)→参照設定(R)のダイアログを開き、
'「Microsoft VBScript Regular Expressions 5.5」にチェックを付けると
'正規表現のRegExpクラスが利用できるようになります。

Dim reg As New RegExp '正規表現クラスオブジェクト

'// 検索条件＝数字以外を抽出
reg.Pattern = "[^0-9０-９:：]"

'// 文字列の最後まで検索する
reg.Global = True

'// 指定セルの数字以外の文字を空文字に置き換える
Find_Time = reg.Replace(s, "")
End Function

Sub Extract_Time()
Dim result As Variant
Dim i As Integer

For i = 1 To 3
result = Find_Time(Sheets("整形").Cells(i, "A").Value)
Sheets("整形").Cells(i, "C") = Val(result) * 0.0166666666666
Next i
End Sub

回答No.2

kkkkkm

Sheets("整形").Cells(i, "C") = Format("00:" & result, "h:n:s")
で試してみてください。

回答No.1

msMike

》 mm:ssは、大文字の場合も小文字の場合もあります
其れ、だう云ふ意味ですか？

例えば、數値
0.058159722
が入力されたセル A1 を
H:MM:SS
に書式設定すると
1:23:45
と表示されるけど、改めてそのセルの書式を眺めると
h:mm:ss
と成って居る事は當ｯたり前田のクラッカーです。
貴方は何を仰って居るのですか？