複数のセルの値を文字列に結合する方法を解説します。
このような処理をしたいときに活用してください。
対象のセルがn×1のセル範囲の場合
冒頭の画像のように、行方向(縦長)データに処理を行う場合です。
この場合は比較的短いコードで実装できます。
まずお使いのExcelがExcel2019以降であれば、
TEXTJOIN関数(区切り文字不要ならCONCAT関数)を、
WorksheetFunctionから呼び出して使用できます。
' ↓「みかん,りんご,いちご」が表示されます。 Msgbox WorksheetFunction.TextJoin(",", False, Range("A1:A3")) ' ↓「みかんりんごいちご」が表示されます。 MsgBox WorksheetFunction.Concat(Range("A1:A3"))
ただし、この手法は「マクロを使う全ユーザーのExcelが新バージョン」である必要があるため、配布の可能性がある場合は慎重に採用を検討してください。
旧Excelと互換性を保って実装できる方法としては、
VBAのJoin関数とExcelのTRANSPOSE関数を組み合わせて使用します。
' ↓「みかん,りんご,いちご」が表示されます。 MsgBox Join(WorksheetFunction.Transpose(Range("A1:A3"))
Join関数は「渡された配列を区切り文字付きで結合する」という、
まさに今回の処理のために用意された関数です。
ただひとつ欠点として、Join関数に渡せる配列は1次元配列限定です。
Rangeオブジェクトは1×nでも2次元配列になってしまうため、
↓このコードでは「型が一致しません」エラーになってしまいます。
MsgBox Join(Range("A1:A3"))
そこでセルを1次元配列に変換するために、
WorksheetFunctionのTranspose関数を利用しました。
TRANSPOSE関数とは「配列の行と列を入れ替える」関数で、
本来は↓このような変換を行うための関数です。
ですがTRANSPOSE関数にはひとつ面白い特徴があり、
「できた配列が1×nだった場合は1次元配列に変換」してくれます。
このため、今回のように「2次元配列を1次元配列に変換」するためにも使えます。
対象のセルが1×nのセル範囲の場合
次はセル範囲が画像のように列方向(横長)データの場合のコードです。
こちらも行方向とほとんど変わらないコードで実装できます。
' TEXTJOIN関数を使用 Msgbox WorksheetFunction.TextJoin(",",False,Range("A1:C1")) ' CONCAT関数を使用 MsgBox WorksheetFunction.Concat(Range("A1:C1")) ' Join+Transpose関数を使用 MsgBox Join(WorksheetFunction.Transpose _ (WorksheetFunction.Transpose(Range("A1:C1")))
唯一変わっているのがJoin+Transpose関数を使う版で、
Transpose関数を2回かませています。
先ほどの説明の通り、Transpose関数は
- できた配列が1×nだった場合は1次元配列に変換
- できた配列がn×1だった場合は2次元配列のまま
という仕様になります。
かといってただRange("A1:C1")では2次元配列になってしまいますので、
少し不思議な式ですが、Transpose関数を2度使うことになっています。
対象のセルが飛び飛びの複数セルの場合
最後に対象のセルが飛び飛びの複数エリアからなるセルの場合です。
このようなセルを文字列結合する場合ですね。
これだとレアケースに感じるかもしれませんが、
以下の「オートフィルターで抽出中のセル」などもこれに該当するため、
そんなに稀な状況のコードではありません。
この場合は、残念ながら1行で行う方法がありません。
VBAのJoin関数が動かないのは当然として、
シート関数のTEXTJOIN(CONCAT)も動きません。
シート上では「複数のセル」を選択できる関数なのですが、
↓このような理屈でVBAで使うことができないのです。
' シート上ではこう書けるため、一見複数セルでも動かせる気がするが・・・ =TEXTJOIN(",",False,A1,B2,C3) ' 上記の式はVBAではこのコードのことであり、これはちゃんと動く MsgBox TEXTJOIN(",", False, Range("A1"), Range("B2"), Range("C3")) ' VBAでRangeオブジェクトを使う場合はこのコードであり、これは動かない MsgBox TEXTJOIN(",", False, Range("A1,B2,C3"))
よってこの複数エリアにまたがるセルを結合する場合は、
愚直に1セルずつループし、&を用いて結合していく方法を取ることになります。
そのコードがこちらです。
Sub 複数セルの文字列を結合する() ' 例：フィルター抽出集のセルだけを結合 Dim 対象セルリスト As Range Set 対象セルリスト = Range("A2:A6").SpecialCells(xlCellTypeVisible) ' すべてのセル値をカンマで結合 Dim 結合文字列 As String Dim cell As Range For Each cell In 対象セルリスト 結合文字列 = 結合文字列 & "," & cell.Value Next ' 最初のカンマを消去 結合文字列 = Mid(結合文字列, 2) End Sub
お手本のような「すべてのセルをForEachで処理」するコードですね。
すべてのセル値を "," と一緒につないでいくコードです。
For Eachの終了段階では、結合文字列は「,」から始まっていますので、
Mid関数で「2文字目以降」を取り出すことで最初の「,」を消しています。
↓のように書いても動きますが、コードが複雑化してしまいがちになるので、
こういった例外処理はなるべくFor文の外に出してあげましょう。
Dim 結合文字列 As String Dim cell As Range For Each cell In 対象セルリスト If 結合文字列 = "" Then ' ←初回のためだけのIf文がFor文を3行⇒7行に増やしてしまう 結合文字列 = cell.Value Else 結合文字列 = 結合文字列 & "," & cell.Value End If Next
汎用関数化
上記のコードは単純ではありますが、
この処理のためにいちいち書くのは面倒です。
この処理をよく行う方は、汎用関数として切り出しておき、
今後は1行で実行できるようにしておきましょう。
そのコードがこちらです。
' 汎用関数 Function Join_Range版(対象セルリスト As Range, 区切り文字 As String) As String Dim cell As Range For Each cell In 対象セルリスト.Cells Join_Range版 = Join_Range版 & 区切り文字 & cell.Value Next Join_Range版 = Mid(Join_Range版, 2) End Function ' 実行例 MsgBox Join_Range版(Range("A2:A6").SpecialCells(xlCellTypeVisible), ",")
これで本家Joinと同じように、1行で実行できるようになりました。
しかもこの関数は、1×n、n×1のセル範囲にも対応しているため、
1度作ってしまえばすべてのセル範囲に対してこの関数だけで対応ができます。
もうTextjoinもTransposeの使用も不要ですね。
こういった「単純だけど書くのが面倒な処理」というのは、
えてして汎用関数が作りやすく、そして使いやすいです。
よく使うコードは汎用関数にして持っておきましょう。
ついでに関数の使い勝手を良くするための小ネタですが、
まず関数名を本家Joinと同じ書き出しにすることで、
この選択肢をCtrl+Spaseで出せるようになります。
元の関数をカスタマイズした関数を作った場合は、
元の関数名から始まる関数名にしておくと便利ですね。
また、Optionalキーワードを使って「引数を省略可能」にし、
Function Join_Range版(対象セルリスト As Range _ , Optional 区切り文字 As String = ",") As String
このような引数にすることで、省略時はカンマで区切ってくれるようになります。
よく使う区切り文字がある方は、その文字を採用してみてください。
汎用関数についての解説はこちらをどうぞ。
www.limecode.jp
