[[20230130144654]]　『指定文字列で分割』（コニャック）　ページの最後に飛ぶ

『指定文字列で分割』（コニャック）

元DATAを指定文字列で分割してセルに書き出すコードを改造し始めました。

分割指定文字が１個の場合は、以下のコードで対応できますが
２個以上の場合が難しくて煮詰まっています。

下の例で考えると1)が終了した時点で
次は、C列を指定文字の「 - 」で分割のように
分割すべき列が変わっていくのですがこの指定の方法で頭を悩ましています。

どのように考えてコードを書けば良いでしょうか　？

元DATA

    |[A]                    
 [1]|001.  あいう - 井上    
 [2]|002.  かきくけこ - 浅田
 [3]|003.  まみ - 小松田

１）A列を「. 」で分割

    |[A]                    |[B]|[C]               
 [1]|001.  あいう - 井上    |  1| あいう - 井上    
 [2]|002.  かきくけこ - 浅田|  2| かきくけこ - 浅田
 [3]|003.  まみ - 小松田    |  3| まみ - 小松田

更にC列を「 - 」で分割して整形

    |[A]                    |[B]|[C]        |[D]   |
 [1]|001.  あいう - 井上    |  1| あいう    |井上  |
 [2]|002.  かきくけこ - 浅田|  2| かきくけこ|浅田  |
 [3]|003.  まみ - 小松田    |  3| まみ      |小松田|

Sub 分割1()

Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = Worksheets("Target")

    Dim rc As VbMsgBoxResult
    '適当に列を消去(書き出しセルの初期化)
    ws.Range("B:N").Clear

    rc = MsgBox("A列に分割する文字列が配置されていますか？", vbYesNo + vbQuestion)
    If rc = vbYes Then
        'MsgBox "次の処理に進みます。", vbInformation
    Else
        MsgBox "「いいえ」が押されたので処理を中止します", vbCritical
        Exit Sub
    End If

    Dim 分割数 As Long
    分割数 = InputBox("分割する数", "個数指定")

    Dim 分割文字 As Variant
    ReDim 分割文字(1 To 分割数)

    Dim c As Long
    For c = 1 To 分割数
        分割文字(c) = InputBox("(" & c & ") " & "分割文字は　？", "文字列指定")
    Next

    Dim maxrow As Long
    maxrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    '分割文字を全角文字で指定しても半角文字に変換
    '全角スペースでも半角スペースでも対応
    'Dim smoji As String
    'smoji = StrConv(InputBox("分割する文字を指定してください。"), vbNarrow)

    Dim i As Long, ii As Long
    Dim cmoji As Variant '配列
    Dim S_Max As Long

    For c = 1 To 分割数
       For i = 1 To maxrow
            cmoji = Split(ws.Cells(i, "A"), 分割文字(c))  '指定文字で分割
            S_Max = UBound(cmoji)  '指定文字の総数

            'B列から分割して書き出す
            For ii = 0 To S_Max
                ws.Cells(i, ii + 2) = cmoji(ii)    '配列の最初はゼロだから B列は ii+2
            Next
        Next
    Next

    ws.Columns("A:J").EntireColumn.AutoFit

End Sub

＜使用 Excel：Excel2021、使用 OS：Windows11 ＞

 いろんなやり方があると思いますが、
 区切り文字を普通使われないような文字列に置き換えちゃう
 というのも一つの方法かと

    Sub test()
      Dim s As String, aryS() As String

      s = "abcd^efgh&ijkl"
      aryS = SplitBy(s, Array("^", "&"))

      Stop
    End Sub

    Function SplitBy(ByVal s As String, delimiter As Variant, Optional limit As Long = -1, Optional Compare As VbCompareMethod = vbBinaryCompare) As String()
       Dim d
       If IsArray(delimiter) Then
          For Each d In delimiter
              s = Replace(s, d, vbBack)
          Next
          SplitBy = Split(s, vbBack, limit, Compare)
       Else
          SplitBy = Split(s, delimiter, limit, Compare)
       End If
    End Function
（´・ω・`） 2023/01/30(月) 15:03:21

´・ω・`さん、アドバイスありがとうございます。

文字列を２つの指定文字列で区切る場合は、
いただいたコードを修正して以下のようなコードで処理できました。

質問の仕方が悪かったのですが指定文字列が３、４と増加する可能性があります。
最初の自前のコードSub 分割1()は、複数の区切り文字を想定して
コードを書き始めています。

´・ω・`さんのコードは、３個以上のコードを想定しての回答となるのでしょうか？
私のスキルでは、´・ω・`さんのコードを３個以上に対応させるは残念ながら出来そうにありません。

どこを直せば３個以上に対応できますか　？

Option Explicit

Sub test()

      Dim s As String, aryS() As String
      Dim i As Long, ii As Long

      For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        s = Cells(i, "A")
        aryS = SplitBy(s, Array(". ", " - "))
            ii = 0
            Cells(i, "B") = aryS(ii)
            Cells(i, "C") = aryS(ii + 1)
            Cells(i, "D") = aryS(ii + 2)
            Stop
      Next

      Stop
    End Sub
    Function SplitBy(ByVal s As String, delimiter As Variant, Optional limit As Long = -1, Optional Compare As VbCompareMethod = vbBinaryCompare) As String()
       Dim d
       If IsArray(delimiter) Then
          For Each d In delimiter
              s = Replace(s, d, vbBack)
          Next
          SplitBy = Split(s, vbBack, limit, Compare)
       Else
          SplitBy = Split(s, delimiter, limit, Compare)
       End If
    End Function

（コニャック） 2023/01/30(月) 15:55:17

 ホントですか...
    Sub test()
       Dim s As String, arys() As String
       s = "A!B#C$D%E&F'G=H~I^J|K\H"
       arys = SplitBy(s, Array("!", "#", "$", "%", "&", "'", "=", "~", "^", "|", "\"))
       For i = LBound(arys) To UBound(arys)
          MsgBox arys(i)
       Next
    End Sub
（´・ω・`） 2023/01/30(月) 16:26:16

 じゃ、ちょっと修正＆使用例も追加

    Sub sample()
       Dim d, buf
       Dim delimiter As New Collection

       d = InputBox("区切り文字を入力してください。")
       Do While d <> ""
          delimiter.Add d
          d = InputBox("区切り文字を入力してください。")
       Loop

       If delimiter.Count = 0 Then Exit Sub

       For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
          buf = SplitBy(Cells(i, "A").Value, delimiter)
          Cells(i, "B").Resize(, UBound(buf) + 1) = buf
       Next
    End Sub

    Function SplitBy(ByVal s As String, delimiter As Variant, Optional limit As Long = -1, Optional Compare As VbCompareMethod = vbBinaryCompare) As String()
       Dim d
       If IsArray(delimiter) Or TypeName(delimiter) = "Collection" Then
          For Each d In delimiter
              s = Replace(s, d, vbBack)
          Next
          SplitBy = Split(s, vbBack, limit, Compare)
       Else
          SplitBy = Split(s, delimiter, limit, Compare)
       End If
    End Function
（´・ω・`） 2023/01/30(月) 16:58:03

＞´・ω・`さんのコードは、３個以上のコードを想定しての回答となるのでしょうか？

すいません、ちゃんと試してから返事すべきでした。
16:26:16　のアドバイスで区切り文字が３個以上でも対応しているのが判りました。

で　指定した区切り文字をどうやったら以下のような形式に出来るのかで先に進まず
"!", "#", "$", "%", "&", "'", "=", "~", "^", "|", "\"

もたもたしているうちに 16:58:03 の追加のアドバイスが書き込まれているのを
２時間ぐらいして気が付きました。

そして　delimiter　と言う初めて聞くコードが有るのに気が付きました。
調べてもSplit関数でのdelimiterはヒットするのですが
delimiter New Collection　では探し方が悪いのかヒットしませんでした。

とりあえず、delimiter　は使ったらどうなるのかをトレースしたくて
自前のコードに追加してみました。

F8でステップ実行しながらなるほどと思いながら続けていくと
以下のコードでエラーが出ました。
SplitBy = Split(s, delimiter, limit, Compare)

引数数が一致していません。又は不正なプロパティを指定しています。

流石に付け焼き刃では上手くいくわけがありませんでした。

以下のコードでおかしな点を指摘してもらえると嬉しいです。

Option Explicit
Sub 分割1()

Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = Worksheets("Target")

    Dim rc As VbMsgBoxResult
    '適当に列を消去(書き出しセルの初期化)
    ws.Range("B:N").Clear

    rc = MsgBox("A列に分割する文字列が配置されていますか？", vbYesNo + vbQuestion)
    If rc = vbYes Then
        'MsgBox "次の処理に進みます。", vbInformation
    Else
        MsgBox "「いいえ」が押されたので処理を中止します", vbCritical
        Exit Sub
    End If

    Dim 分割数 As Long
    分割数 = Application.InputBox("分割する数", _
                                    "個数指定", _
                                    Type:=1)
    'cancelを選択した場合
    If 分割数 = 0 Then
        MsgBox "処理を中止します。"
        Exit Sub
    End If

    Dim 分割文字 As Variant
    ReDim 分割文字(1 To 分割数)

    Dim c As Long
    Dim delimiter As New Collection
    For c = 1 To 分割数
        分割文字(c) = Application.InputBox(prompt:="(" & c & ") " & "分割文字は　？", _
                                            Title:="文字列", _
                                            Type:=2)
        'cancelを選択した場合
        If 分割文字(c) = False Then
            MsgBox "処理を中止します。"
            Exit Sub
        End If
        delimiter.Add 分割文字(c)
    Next
   c = c - 1　　'------- ｃがなぜだか一つ増えるので－１　　------
    Stop
    Dim maxrow As Long
    maxrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Dim s As String, arys() As String
    Dim i As Long, ii As Long

    For i = 1 To maxrow
        s = Cells(i, "A").Value
        arys = SplitBy(s, delimiter)
        Cells(i, "B").Resize(, UBound(arys) + 1) = arys
      Next

    ws.Columns("A:J").EntireColumn.AutoFit

End Sub

   Function SplitBy(ByVal s As String, delimiter As Variant, Optional limit As Long = -1, Optional Compare As VbCompareMethod = vbBinaryCompare) As String()
       Dim d
       If IsArray(delimiter) Then
          For Each d In delimiter
              s = Replace(s, d, vbBack)
          Next
          SplitBy = Split(s, vbBack, limit, Compare)
       Else
          SplitBy = Split(s, delimiter, limit, Compare)
       End If
    End Function

（コニャック） 2023/01/30(月) 19:27:01

 >delimiter　と言う初めて聞くコード
変数名ですよ。Dimで宣言してます。
（フォーキー） 2023/01/30(月) 19:44:43

 被りますがそのままアップします。

 New Collection
 または
 ＶＢＡ　New Collection
 で調べてみてください。

 >Dim delimiter As New Collection

 はユーザーが勝手に？命名する変数名
 ですので、検索してヒットしないのは当たり
 前です。
（ＭＫ） 2023/01/30(月) 19:48:53

ついでに... ^^;

デリミタとは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典
https://wa3.i-3-i.info/word11621.html

（白茶） 2023/01/30(月) 20:21:09

 arys = SplitBy(s, delimiter) 
 を
 arys = SplitBy(s, 分割文字)
 にしたら大丈夫だと思いますけど
 たぶんですが

 かなり汎用的に使えるように作ってるつもりなんですけど
（´・ω・`） 2023/01/30(月) 20:22:52

 あ、2023/01/30(月) 16:58:03のはちょっとだけバージョンアップしてるので、
 そっちつかわないとcollectionつかえないです
（´・ω・`） 2023/01/30(月) 22:58:28

皆さん、アドバイス感謝します。

Dim delimiter As New Collection

delimiter.Add d

お陰様で何となく理解できました。

      つまり、新規にdelimiterと言うコレクションを宣言して
      （コレクションとは、複数の同一オブゼクトを集めたもの）
      delimiterに　＜区切り文字(d)＝メンバ＞　を次々に追加していって

      結果、指定した区切り文字が全てdelimiterにItemとして登録される

＞arys = SplitBy(s, delimiter)
＞を
＞arys = SplitBy(s, 分割文字)
＞そっちつかわないとcollectionつかえないです

分割文字　に変更してうまく処理出来ました。

結果は、出たのですがSplitByを求めるFunctionの中身がブラックボックスで
現状どんな事をしているのかが全く判っていません。

なので
s = Cells(i, "A").Value　以降のコードが理解できずにいます。

メカニズムを判りやすく説明いただけないでしょうか？
（コニャック） 2023/01/31(火) 06:10:38

 なにをやっているかは一番最初に書きました

 どのようにやっているかは、コードは意味のない呪文じゃなくて意味のある命令文なので
 辞書（ヘルプ）を見れば、書いてあります。
 １０行程度のコードですし、基本的なステートメントしかつかってないですし。

 ステップ実行すれば、動作を実際に確かめる事が出来ます。

 正直、何を解説すればいいのか分からないです

 どこの部分が分からないですか？具体的に書いてもらえると解説できます
（´・ω・`） 2023/01/31(火) 09:48:18

´・ω・`さん、

ローカルウィンドウの変数の変化を
ステップ実行で確認しながら見ていく事で
何がFunctionで行われているかは大体理解できました。

動画をとって、必要な箇所を画像で抜きだしてわからないところは
ネットの情報で調べながら見ていくと
以下のような事がFunctionで行われていると思いますが合っていますか　？

まずSから区切り文字(d)を検索して
順番に普通使われないような文字列「・」に置き換えてｓに格納
「・」　は、vbBack（システム定数文字列）でバックスペース文字（chr(8)）の事

s = Replace(s, d, vbBack)

次にsをSplitByでvbBack(「・」)で区切って抽出する

SplitBy = Split(s, vbBack, limit, Compare)
は、パラメーターが省略されているので以下と同じ

SplitBy = Split(s, vbBack)

      limit: すべての要素を含んだ配列を返す（既定値）

      Compare: バイナリモードで比較（既定値）
               バイナリモードは「大文字と小文字」「全角と半角」「ひらがなとカタカナ」を区別

最終的にFunctionからリターンした時点で
SplitByに格納されていた値がarys()に置き換わるので
分割文字で切り分けられた文字列がarys()配列に抽出される

'-------------------------------------------------
今一つ分からないのは、以下のElseの場合

   Else
          SplitBy = Split(s, delimiter, limit, Compare)

今回は、１回もelse以降は使用されませんでした。
これは、どんな時に利用される事を前提に記載されているのでしょうか？
（コニャック） 2023/01/31(火) 11:44:18

 >どんな時に利用される事を前提に
 If文がFalseになるのは、delimiter が文字列（文字列の配列でもコレクションでもなく）で
 与えられたときです。

 Split関数をそのままSplitByで置き換えできるようにそのようにしています。
 お遊びなので、気にしないでいいです
（´・ω・`） 2023/01/31(火) 13:09:36

＞お遊びなので、気にしないでいいです

判りました。

今回の私の場合は、else　以降が行われる事は無いと理解しました。

色々教えて頂きありがとうごじました。

（コニャック） 2023/01/31(火) 13:34:11

＞色々教えて頂きありがとうごじました。

失礼しました。
TYPEミスを修正します。

色々教えて頂きありがとうございます。
（コニャック） 2023/01/31(火) 13:54:32

コメント返信：

[ 一覧(最新更新順) ]

[[20230130144654]] 『指定文字列で分割』（コニャック） ページの最後に飛ぶ

[[20230130144654]]　『指定文字列で分割』（コニャック）　ページの最後に飛ぶ