[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定文字列で分割』(コニャック)
元DATAを指定文字列で分割してセルに書き出すコードを改造し始めました。
分割指定文字が1個の場合は、以下のコードで対応できますが
2個以上の場合が難しくて煮詰まっています。
下の例で考えると1)が終了した時点で
次は、C列を指定文字の「 - 」で分割のように
分割すべき列が変わっていくのですがこの指定の方法で頭を悩ましています。
どのように考えてコードを書けば良いでしょうか ?
元DATA
|[A] [1]|001. あいう - 井上 [2]|002. かきくけこ - 浅田 [3]|003. まみ - 小松田
1)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
文字列を2つの指定文字列で区切る場合は、
いただいたコードを修正して以下のようなコードで処理できました。
質問の仕方が悪かったのですが指定文字列が3、4と増加する可能性があります。
最初の自前のコードSub 分割1()は、複数の区切り文字を想定して
コードを書き始めています。
´・ω・`さんのコードは、3個以上のコードを想定しての回答となるのでしょうか?
私のスキルでは、´・ω・`さんのコードを3個以上に対応させるは残念ながら出来そうにありません。
どこを直せば3個以上に対応できますか ?
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 のアドバイスで区切り文字が3個以上でも対応しているのが判りました。
で 指定した区切り文字をどうやったら以下のような形式に出来るのかで先に進まず
"!", "#", "$", "%", "&", "'", "=", "~", "^", "|", "\"
もたもたしているうちに 16:58:03 の追加のアドバイスが書き込まれているのを
2時間ぐらいして気が付きました。
そして 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 '------- 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 または VBA New Collection で調べてみてください。
>Dim delimiter As New Collection
はユーザーが勝手に?命名する変数名 ですので、検索してヒットしないのは当たり 前です。 (MK) 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
なにをやっているかは一番最初に書きました
どのようにやっているかは、コードは意味のない呪文じゃなくて意味のある命令文なので 辞書(ヘルプ)を見れば、書いてあります。 10行程度のコードですし、基本的なステートメントしかつかってないですし。
ステップ実行すれば、動作を実際に確かめる事が出来ます。
正直、何を解説すればいいのか分からないです
どこの部分が分からないですか?具体的に書いてもらえると解説できます (´・ω・`) 2023/01/31(火) 09:48:18
ローカルウィンドウの変数の変化を
ステップ実行で確認しながら見ていく事で
何がFunctionで行われているかは大体理解できました。
動画をとって、必要な箇所を画像で抜きだしてわからないところは
ネットの情報で調べながら見ていくと
以下のような事がFunctionで行われていると思いますが合っていますか ?
まずSから区切り文字(d)を検索して
順番に普通使われないような文字列「・」に置き換えてsに格納
「・」 は、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)
今回は、1回も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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.