前後の入替（区切りが2回出現する場合）

質問

B4セル下記のようなファイル名だとして
　01 - Home Wide - Number.mp3

それをD4に
　 01 - Number - Home Wide.mp3
のように前から2番めの「 - 」の前後で前後を入れ替えた形に整形したいのですが
「 - 」が2回出現するので上手く処理できないでいます。

どのような関数になりますか　？

1回なら、
C4：＝Left(B4，Len(B4)-4）
D4: =RIGHT(C4,LEN(C4)-FIND("-",C4))&" - "&LEFT(A1,FIND(" - ",A1)-1)&".mp3"
で処理できますが、
2回出現する場合の考え方が分かりません。

---------------------------
できれば、VBAでのコードも教えてもらえれば
　余分な列（C列）を作らなくて良いので嬉しいです。

回答No.3

kkkkkm · ベストアンサー

No2のお礼（2021/07/24 15:46)にある

Sub 区切りで前後入替()
Dim Ext As String
Dim IRow, I As Single

IRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得

For I = 4 To IRow
Ext = "." & Split(Cells(I, "B"), ".")(1)
Cells(I, "C").Value = Split(Cells(I, "B").Value, " - ")(0) & " - " & _
Replace(Split(Cells(I, "B").Value, " - ")(2), Ext, "") & " - " & _
Split(Cells(I, "B").Value, " - ")(1) & Ext
Next I

End Sub

これで実行して問題無く変換できました…ファイル名は質問にあるファイル名だけをB列に並べただけですが。

1行では上手くできたということは最低でもB4のデータは良かったという事だと思いますので、何行目までできたのか、できていないデータに何か他と違う要素がないか確認してみてください。

Dim IRow, I As Single
は
Dim IRow As Long, I As Long
のように両方とも型指定しないとIRowは型指定無しになると思います。

合間に関数もちょっと考えたのでおまけです
C4：＝Left(B4，Len(B4)-4
の場合

=FIND("☆", SUBSTITUTE(C4, "-", "☆", 2))
で2個目の「-」の位置がわかりますので(☆は元の文字列に存在することが無いと思われる適当な文字や記号で)

=LEFT(C4,FIND("-",C4)) & RIGHT(C4,LEN(C4)-FIND("☆",SUBSTITUTE(C4,"-","☆",2))) & " " & MID(C4,FIND("-",C4),FIND("☆",SUBSTITUTE(C4,"-","☆",2))-FIND("-",C4)-1) & ".mp3"

で

お礼

回答、感謝します。

>できていないデータに何か他と違う要素がないか確認してみてください。

B列にある約80個ほどある変更前のDATAを最初から見てみたら
　46番目に2番めの「 - 」の次が空白で何も無いファイルが有りました。
　これが原因でエラーが出ました。

　「「-」の前後に半角スペースが存在するファイル名で間違いは有りません。」と
　断定していたのにお粗末な原因で申し訳ないです。

　46番目のファイルを手動で訂正して、80個のファイル全てエラー無く処理出来るのを確認しました。

Dimの宣言での間違いの指摘感謝します。
　　（早速、修正しました。）

おまけの☆印のワークシート関数ですが難解なので
　2行に分けて処理するのが私の理解できる現在の限界ですが
　無い文字を探させるは勉強になります。

------------------------------
okwaveは一度投稿すると、訂正が出来ないのが残念です。
（又、時系列に古い書き込みから下に流れていかないので
　　　見直すのが大変です。）

No2は、最初「補足」に書き込んだ後で
　　Cells(I, "C4").Value =　は、Cells(I, "C").Value =　の間違いと気づいて
　　修正できないので「お礼」の方に書き込みました。
　　結果、同じような記事が２つ記載されて混乱されたこと思います。
　　不手際、お詫び申し上げます。

回答No.4

kkkkkm

> okwaveは一度投稿すると、訂正が出来ないのが残念です。

まさに、その通りだと思います。訂正とか自分の回答削除とかできてもいいと思うんですが、そうすると問題になることもあるんでしょうね。

お礼

今回も、アドバイスを頂きありがとうございました。
おかげさまで無事処理できました。

回答No.2

kkkkkm

> 「実行エラー：インデックスが有効範囲に有りません。」と表示されます。

「-」の前後に半角スペースが質問のファイル名にありましたが実際のファイル名にない場合は

Split(Range("B4").Value, " - ")
の " - "の前後の半角スペースは取り除いてください。

お礼

すいません。

ケアレスミスで元データーに誤りがありました。
「-」の前後に半角スペースが存在するファイル名で間違いは有りません。
（No.2の訂正は必要ないと思います。）

ケアレスミスを修正して1行では上手く変換できたので
下記のようにコードを修正しました。

同じ下記で違うエラーが発生します。
Cells(I, "C").Value = Split(Cells(I, "B").Value, " - ")(0) & " - " & _
Replace(Split(Cells(I, "B").Value, " - ")(2), Ext, "") & " - " & _
Split(Cells(I, "B").Value, " - ")(1) & Ext

実行エラー（9）
「インデックスが有効ではありません。」

-----------------------------------------------------------------------
Sub 区切りで前後入替()
Dim Ext As String
Dim IRow, I As Single

IRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得

For I = 4 To IRow
Ext = "." & Split(Cells(I, "B"), ".")(1)
Cells(I, "C").Value = Split(Cells(I, "B").Value, " - ")(0) & " - " & _
Replace(Split(Cells(I, "B").Value, " - ")(2), Ext, "") & " - " & _
Split(Cells(I, "B").Value, " - ")(1) & Ext
Next I

End Sub

補足

すいません。

ケアレスミスで元データーに誤りがありました。
「-」の前後に半角スペースが存在するファイル名で間違いは有りません。
（No.2の訂正は必要ないと思います。）

ケアレスミスを修正して1行では上手く変換できたので
下記のようにコードを修正しました。

同じ下記で違うエラーが発生します。
Cells(I, "C4").Value = Split(Cells(I, "B").Value, " - ")(0) & " - " & _
Replace(Split(Cells(I, "B").Value, " - ")(2), Ext, "") & " - " & _
Split(Cells(I, "B").Value, " - ")(1) & Ext

実行エラー（1004）
「アプリケーション定義またはオブゼクト定義のエラーです。」

-----------------------------------------------------------------------
Sub 区切りで前後入替()
Dim Ext As String
Dim IRow, I As Single

IRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行を取得

For I = 4 To IRow
Ext = "." & Split(Cells(I, "B"), ".")(1)
Cells(I, "C4").Value = Split(Cells(I, "B").Value, " - ")(0) & " - " & _
Replace(Split(Cells(I, "B").Value, " - ")(2), Ext, "") & " - " & _
Split(Cells(I, "B").Value, " - ")(1) & Ext
Next I

End Sub

回答No.1

kkkkkm

「 - 」が必ず2回出現するのでしたら
A1データを入れ替えてA2に
Sub Test()
Dim Ext As String
Ext = "." & Split(Range("A1"), ".")(1)
Range("A2").Value = Split(Range("A1").Value, " - ")(0) & " - " & _
Replace(Split(Range("A1").Value, " - ")(2), Ext, "") & " - " & _
Split(Range("A1").Value, " - ")(1) & Ext

End Sub

お礼

回答ありがとうございます。

>「 - 」が必ず2回出現するのでしたら
　　はい、必ず2回出現します。

>A1データを入れ替えてA2に
　　　B4に元ファイル名でC4に変更後のファイル名なので
　　　下記のようにコードを変更しました。

以下で「実行エラー：インデックスが有効範囲に有りません。」と表示されます。

Range("C4").Value =　------　Split(Range("B4").Value, " - ")(1) & Ext

-----------------------------------------------------------------------
Sub 区切りで前後入替()
Dim Ext As String
Ext = "." & Split(Range("B4"), ".")(1)
Range("C4").Value = Split(Range("B4").Value, " - ")(0) & " - " & _
Replace(Split(Range("B4").Value, " - ")(2), Ext, "") & " - " & _
Split(Range("B4").Value, " - ")(1) & Ext
End Sub