[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時間と文字列を分割する』（動画ファン）
数値と文字列をセル毎に分割する場合は、
下記のコードでできますが、
時間（9：03）と文字列を分割する場合はどのようになりますか？
’9：03は9分03秒です。
例えば、
A2セルにBUNRI 9:03 なら
B2に00:09:03
C2にBUNRI
B2には、hh:mm:ss形式で表示したい
Sub test()
Dim i As Long
For i = 2 To 13
Cells(i, 2) = Val(Cells(i, 1))
Cells(i, 3) = Replace(Cells(i, 1), Cells(i, 2), "")
Next
End Sub
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
おはようございます ＾＾
一例しか解りませんので。。。一件のみ処理で。。。＾＾；
m(_ _)m
Option Explicit
Sub xa1()
Dim mStr$, vs1, vs2, mt#
mStr = StrConv("BUNRI 9:03", vbNarrow)
vs1 = Split(mStr, Chr(32))
vs2 = Split(vs1(1), ":")
mt = TimeSerial(0, vs2(0), vs2(1))
MsgBox vs1(0) & Chr(13) & Format(mt, "hh:mm:ss")
End Sub
（隠居Z） 2022/01/02(日) 12:04
・A2セルに"BUNRI 9:03"なら ・B2に「00:09:03」（表示形式を"hh:mm:ss"に変更） ・C2にBUNRI
これだけなら、
(1) A2セルの値をSplit関数でぶった切って (2) B2セル・・・・シリアル値に変換して計算のうえ出力し、表示形式を変更 (3) C2セル・・・・Split関数でぶった切った結果をそのまま出力
でOKでしょう。
Sub 研究用()
Dim buf As Variant
Stop 'ブレークポイントの代わり
buf = Split(Range("A2").Value, " ")
Range("B2").Value = CDate(buf(1)) / 60
Range("B2").NumberFormatLocal = "hh:mm:ss"
Range("C2").Value = buf(0)
End Sub
（もこな２） 2022/01/02(日) 12:35
A2が The Opening 0:15 の場合
文字列に半角スペースがあるので簡単にはspritで分割できません。
Sub 研究用()
Dim i As Long
Dim buf As Variant
For i = 2 To 13
buf = Split(Cells(2, "A").Value, " ")
Range("B2").Value = CDate(buf(1)) / 60
Range("B2").NumberFormatLocal = "hh:mm:ss"
Range("C2").Value = buf(0)
Next
End Sub
（動画ファン） 2022/01/02(日) 12:43
ちなみに、Split関数もNumberFormatLocalプロパティも過去トピックで取り扱っていますが、読み返してみましたか？
（もこな２） 2022/01/02(日) 12:49
2022/01/02(日) 12:43 で
A2が The Opening 0:15 の場合 と例を示しましたが
これではだめですか？
（動画ファン） 2022/01/02(日) 13:00
時間部分が一番末尾にあるのは固定だというなら、Replace関数を使って時間部分（と直前の" ")を取り去ればよいですね。（これも過去トピックで学習済みかと思いますが）
Sub 研究用2()
Dim buf As Variant
Stop 'ブレークポイントの代わり
buf = Split(Range("A2").Value, " ")
Range("B2").Value = CDate(buf(UBound(buf))) / 60
Range("B2").NumberFormatLocal = "hh:mm:ss"
Range("C2").Value = Replace(Range("A2").Value, " " & buf(UBound(buf)), "")
End Sub
（もこな２） 2022/01/02(日) 13:02
上記の意味が理解できません。
何をはじめから記載すれば良かったのでしょうか ？
時間が最初にある場合は、現在コードが出来ていて
おっしゃるように一番末尾にある場合に特化したコードでも良いと思います。
研究用2を以下のように変更して試運転してみました。
結果、テスト用の13のDATAの内
A列の1-8行目までは上手く処理できましたが
A9で文字列が 「Equinoxe 4 28:45」 の場合なぜだか？
実行エラー 13 ：型が一致しません。のエラーが出ます。
同じような型式の下記は、問題なく処理されました。
「Oxygene 19 12:28」
Sub 研究用2()
Dim buf As Variant
Dim i As Long
For i = 2 To 13
buf = Split(Cells(i, "A").Value, " ")
Cells(i, "B").Value = CDate(buf(UBound(buf))) / 60
Cells(i, "B").NumberFormatLocal = "hh:mm:ss"
Cells(i, "C").Value = Replace(Cells(i, "A").Value, " " & buf(UBound(buf)), "")
Next
End Sub
’-----------------
実際は、文字列から時間相当分（mm:ssやh:mm:ss)を隣のセル（b列）に抜き出して
そのまた隣（C列）に時間相当を除いた文字列を抜き出したい
例えば
A2 01 12:03 Litle Song (angel_05)
B2 00:12:03
C2 01 Litle Song (angel_05)
A2 02 1:12:03 man to go
B2 01:12:03
C2 02 man to go
（動画ファン） 2022/01/02(日) 13:57
【結果例】
A列 B C
2行 01 12:03 Litle Song (angel_05) 00:12:03 01 Litle Song (angel_05)
3 02 1:12:03 man to go 01:12:03 02 man to go
4 Equinoxe 4 28:45 00:28:45 Equinoxe 4
参考コードです。
Sub test()
Dim r As Range
Dim re As Object
Dim m As Object
Dim s As String
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "(\d{1,2}:\d{1,2}:\d{1,2})|(\d{1,2}:\d{1,2})"
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
s = r.Value
Set m = re.Execute(s)
If m.Count = 1 Then
r.Offset(, 1).NumberFormatLocal = "hh:mm:ss"
If m(0).submatches(0) <> Empty Then
r.Offset(, 1).Value = TimeValue(m(0).Value)
Else
r.Offset(, 1).Value = TimeValue("0:" & m(0).Value)
End If
s = Replace(re.Replace(s, ""), " ", " ")
r.Offset(, 2).Value = s
End If
Next
End Sub
（γ） 2022/01/02(日) 14:56
参考に
Sub Test()
Dim i As Long, j As Long, v As Variant, strTime As String, n As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
v = Split(Cells(i, 1).Value)
For j = 0 To UBound(v)
If InStr(v(j), ":") Then
strTime = v(j)
Exit For
End If
Next
n = Len(strTime) - Len(Replace(strTime, ":", ""))
Cells(i, 2).NumberFormatLocal = "hh:mm:ss"
Cells(i, 2).Value = IIf(n = 1, "00:" & strTime, strTime)
Cells(i, 3).Value = WorksheetFunction.Trim(Replace(Cells(i, 1), strTime, ""))
Next
End Sub
（ピンク） 2022/01/02(日) 15:44
■１
＞上記の意味が理解できません。
＞何をはじめから記載すれば良かったのでしょうか ？
たびたび、誤解させてしまいましたね。すみません。
文字列に半角スペースがあり得るなら【はじめから】示しておいてほしいということをお伝えしたかった次第です。
■２
結局、このような条件ですかね。
・時間に該当する部分はどこに出てくるかわからない ・時間に相当する部分は「mm:ss」「hh:mm:ss」いずれかのパターンである ・時間に相当する部分（":"が含まれる単語）は2つ以上存在することはない ・単語(時間)の間は" "で区切られている
______________A______________
1
2 BUNRI 9:03
3 The Opening 0:15
4 01 12:03 Litle Song (angel_05)
5 02 1:12:03 man to go
6 Equinoxe 4 28:45
7 時間 なし
■３
正規表現苦手なので、少々力技ですが
(1) " "を区切り文字として切り分けてから、各単語を巡回し (2) ":"が含まれる単語があれば、一旦その単語を記憶 (3) (2)で覚えた単語があれば、":"の個数に応じて"0:"を追加して出力 (4) (2)の結果にかかわらず、時間部分をReplace関数を使って除外して、ダブってしまう" "をワークシート関数のTrimで処理
と考えれば↓のようでもいけそうですね。
Sub 研究用3()
Dim 行 As Long
Dim 時間部分 As String
Dim tmp As Variant
Stop 'ブレークポイントの代わり
Range("B2:B6").NumberFormatLocal = "hh:mm:ss"
For 行 = 2 To 7
時間部分 = ""
For Each tmp In Split(Cells(行, "A").Value, " ")
If tmp Like "*:*" Then
時間部分 = tmp
If UBound(Split(時間部分, ":")) > 1 Then
Cells(行, "B").Value = 時間部分
Else
Cells(行, "B").Value = "0:" & 時間部分
End If
Exit For
End If
Next tmp
Cells(行, "C").Value = Trim(Replace(Cells(行, "A").Value, 時間部分, ""))
Next 行
End Sub
【処理結果】
______________A_______________ ___B___ ___________C____________
1
2 BUNRI 9:03 0:09:03 BUNRI
3 The Opening 0:15 0:00:15 The Opening
4 01 12:03 Litle Song (angel_05) 0:12:03 01 Litle Song (angel_05)
5 02 1:12:03 man to go 1:12:03 02 man to go
6 Equinoxe 4 28:45 0:28:45 Equinoxe 4
7 時間 なし 時間 なし
■４
「2022/01/02(日) 12:43」「2022/01/02(日) 13:57」に提示いただいたコードを拝見して。
老婆心ながらインデントの付け方を再考されたほうがよいかもしれません。
■５
＞型が一致しません。のエラーが出ます。
じっくり研究すれば理解できるとおもいますが↓がエラーになっているわけですよね。
CDate("28:45")
要は、エクセル君は↑を28時45分という"時刻"だとは解釈できないということですね。
（もこな２） 2022/01/02(日) 16:45
時刻の記載がない場合も想定して
Sub Test2()
Dim i As Long, j As Long, v As Variant, strTime As String, n As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
v = Split(Cells(i, 1).Value)
strTime = ""
For j = 0 To UBound(v)
If InStr(v(j), ":") Then strTime = v(j)
Next
n = Len(strTime) - Len(Replace(strTime, ":", ""))
Cells(i, 2).NumberFormatLocal = "hh:mm:ss"
Cells(i, 2).Value = IIf(n = 1, "00:" & strTime, strTime)
Cells(i, 3).Value = WorksheetFunction.Trim(Replace(Cells(i, 1), strTime, ""))
Next
End Sub
（ピンク） 2022/01/02(日) 19:02
誤 Cells(行, "C").Value = Trim(Replace(Cells(行, "A").Value, 時間部分, "")) 正 Cells(行, "C").Value = WorksheetFunction.Trim(Replace(Cells(行, "A").Value, 時間部分, ""))
ですね。失礼しました。
（もこな２ ） 2022/01/02(日) 19:49
所用の為及びコードの検証に時間がかかりました。
（今朝より検証再開）
’--------------------------------------
「もこな２」さん、
＞■1
＞文字列に半角スペースがあり得るなら【はじめから】示しておいてほしいということをお伝えしたかった次第です。
【はじめから】の意味理解しました。
私としては、最初の投稿で例として「A2セルにBUNRI 9:03 なら」と上げたので
BUNRIと9：03の間に半角スペースが存在していたのですが
説明不足でお手を煩わせる事となりすいません。
＞■2
＞結局、このような条件ですかね。
まとめられた4つ（・）の条件です。
＞■3
提示いただいたコードで満足な処理ができました。
ありがとうございます。
＞■４
＞インデントの付け方を再考されたほうがよいかもしれません。
ここのインデントの付け方が基準が良くわからないので
「インデントなし」を基本にしたいと思います。
＞2022/01/02(日) 16:45に提示したコードに誤りがありました。
私の環境では、Trimeの前に「WorksheetFunction.」を付加し無くてもエラー無く処理されました。
（付加しても同じく正常に処理されたので付加したコードに差し替えました。）
’--------------------------------------
「γさん」、test（）は希望どうりに処理できました。
ありがとうございます。
'--------------------------------------
「ピンク」さん、test(),test2()は希望どうりに処理できました。
（時刻の記載がない場合も想定して頂き感謝です。）
ありがとうございます。
（動画ファン） 2022/01/03(月) 08:52
えっと・・・何をやってるちゃんとステップ実行して確認されてますか？
始めに提示いただいたほうは↓のように半角スペース（説明のために★に置換しました）"1"つで区切られてますよね。
BUNRI★9:03
なので↓のようにと考えて提示した次第です。
・単純にSplit関数で【2つに】わけて ・後半は"hh:mm"と解釈されてしまうので、60で割って"mm:ss"に変換 ・前半はそのまま出力
次に提示されたのは↓のように半角スペース"2"つでした。
The★Opening★0:15
この時点で単純に【2つに】わけて処理するアプローチは使えません。
なので（そのようなケースがあるなら）始めから説明してほしい。と言ったのです。
■７
＞提示いただいたコードで満足な処理ができました。
＞ありがとうございます。
こちらの意図としては、完成品のプレゼントをしたわけではありません。
ちゃんと【ステップ実行】などにより研究してみてください。
※何度もコメントしましたが、過去ログに出てきた技術も使ってますよ。 忘れてしまったなら読み返されてはいかがでしょうか？ [[20210209170331]] 『文字型で表示されない（文字型で表示したい）』（動画ファン）
■８
＞「インデントなし」を基本にしたいと思います。
VBAの場合、プログラム的にインデントが意味を持つものではありません（あってもなくてもちゃんと実行できる）。
一方で、インデントを適切につけておくとコード全体の構造が把握しやすくなり、ご自身のデバッグ作業に寄与すると思います。
よって私自身はインデントを付けることをお勧めしますが、ご本人が納得できるのが一番なのでお好きにどうぞ。
（ただ、インデントついてなかったら指摘しちゃうとは思いますが）
※気が変わってインデントについて学んでみようと思う場合は↓などを読んでみてはどうでしょうか？
【インデント】 https://excel-ubara.com/excelvba4/EXCEL_VBA_444.html https://thom.hateblo.jp/entry/2018/02/26/234247 https://kabu-macro.com/detail.php?dir=word&dir2=sa-so&uri=zisage
■９
＞私の環境では、Trimeの前に「WorksheetFunction.」を付加し無くてもエラー無く処理されました。
気づきにくいですがVBAのTrim関数だと↓のようになったと思います。
02 1:12:03 man to go → 02 man to go 【VBAのTrim関数】 02 1:12:03 man to go → 02 man to go 【ワークシートのTrim関数】
時間部分だけを除くと半角スペースが２個続くことになるが、VBAのTrim関数では文字列中の余分なスペースは処理されない。
（もこな２ ） 2022/01/03(月) 15:03
| ここのインデントの付け方が基準が良くわからないので | 「インデントなし」を基本にしたいと思います。 誤解されている気配がありますが、 インデントは、別に質問掲示板が定めるものではありません。（この掲示板でも基準など無いです）
コードを書く場合に多くの方が使っている、段落付けのことです。 インデントをつけることによって、作成者や利用者（読む方。将来の自分も含まれます）の 効率を上げることになることが共通のコンセンサスになっているものです。（細かい流儀の差はあります） 他人のためではなく、主としてご自分のためになるものです。 ご参考まで。
TRIMの話の補足です。 (参考：ヘルプから引用) | TRIMワークシート関数 | 各単語間のスペースは 1 つ残し、不要なスペースをすべて削除します。 | | VBAのTrim関数 | 先頭と末尾の両方のスペースを除いたコピー (Trim) を格納する Variant (String) を返します。 （γ） 2022/01/03(月) 17:07
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.