【VBA】TEXTJOIN関数をExcel2013以前のエクセルで使いたい

TEXTJOIN関数を古いバージョンのエクセルでも使えるようにする、ユーザー定義関数を紹介します。

とっても便利なTEXTJOIN関数ですが、Excel2016以降でしか使えません。
※Office365のExcel2016。通常のExcel2016では使えません。

でも、うちの会社はExcelのバージョンがマチマチです。

使用者のExcelのバージョンが2013以前だと、TEXTJOIN関数が使えず、面倒くさく、わかりにくい式を組まないといけなくなりますね。

便利な関数なのに、もったいないです。

目次

例.TEXTJOIN関数を使う場合と使わない場合

サンプル

① 項目を「,」で区切ってつなげる

TEXTJOIN関数

=TEXTJOIN(“,”,TRUE,A2:J2)

結果
大根,人参,キャベツ,白菜,もやし,玉ねぎ,きゅうり,トマト,ごぼう,蓮根

Excel2013以前のバージョン

=A2&”,”&B2&”,”&C2&”,”&D2&”,”&E2&”,”&F2&”,”&G2&”,”&H2&”,”&I2&”,”&J2

結果
大根,人参,キャベツ,白菜,もやし,玉ねぎ,きゅうり,トマト,ごぼう,蓮根

② 区切る項目を「と」と「、」とで交互にする

TEXTJOIN関数

=TEXTJOIN({“と”,”、”},TRUE,A2:J2)

結果
大根と人参、キャベツと白菜、もやしと玉ねぎ、きゅうりとトマト、ごぼうと蓮根

Excel2013以前のバージョン

=A2&”と”&B2&”、”&C2&”と”&D2&”、”&E2&”と”&F2&”、”&G2&”と”&H2&”、”&I2&”と”&J2

結果
大根と人参、キャベツと白菜、もやしと玉ねぎ、きゅうりとトマト、ごぼうと蓮根

TEXTJOIN関数が使えたらいいのに!

となりますね

Excel2013以前のバージョンでTEXTJOIN関数を使う方法

TEXTJOIN関数の中に数式を入れたり、区切り文字の参照先をセル範囲にしたりと複雑なものには対応していません(後述)。

新規のExcelファイルを開き、VBAを記述する

「開発」タブ → Visual Basic (もしくは Alt + F11)

「挿入」タブ → 標準モジュール

出てきたウィンドウにVBAを記述する。

Function TEXTJOIN(Delim, Ignore As Boolean, ParamArray par())

Dim i As Integer
Dim tR As Range

TEXTJOIN = “”
For i = LBound(par) To UBound(par)
      If TypeName(par(i)) = “Range” Then
            For Each tR In par(i)
                  If tR.Value <> “” Or Ignore = False Then
                        TEXTJOIN = TEXTJOIN & Delim & tR.Value2
                  End If
            Next
      Else
            If par(i) <> “” Or Ignore = False Then
                  TEXTJOIN = TEXTJOIN & Delim & par(i)
            End If
      End If
Next

TEXTJOIN = Mid(TEXTJOIN, Len(Delim) + 1)

End Function

エクセルに戻り、名前をつけて保存する(今回はTEXTJOIN.xlamとする)

Excelアドイン(.xlamファイル)として保存

ファイルはアドイン用のフォルダに保存されます。
例 C:\Users*****\AppData\Local\Packages\Microsoft.Office.Desktop_8wekyb3d8bbwe\LocalCache\Roaming\Microsoft\AddIns\TEXTJOIN.xlam

VBAの解説

TEXTJOIN(区切り文字,空白について,配列)

TEXTJOINの中身を空にする
配列(つなげたい文字列)の最小個数~最大個数でループする
      もし配列の型名が「Range」だったら(セルが指定されていたら)
            配列内にあるそれぞれのセルにおいて
                  もし「セル値が空欄でない」、または「空白を無視しない」のであれば
                        TEXTJOIN = TEXTJOIN & 区切り文字 & セル値
                        (セル値が空欄で、かつ空白を無視するのであれば何も処理しない)

      もし配列の型名が「Range」でなかったら(文字列が記入されていたら)
            もし「配列の文字列が空白でない」、または「空白を無視しない」のであれば
                  TEXTJOIN = TEXTJOIN & 区切り文字 & 配列の文字列
                  (配列の文字列が空欄で、かつ空白を無視するのであれば何も処理しない)

ここまででループする

TEXTJOIN = 上記で作成したTEXTJOINの、1番目の区切り文字の次の文字から最後までの文字列

Excelで常に使用できるようにする(アドインの設定)

先ほど作ったアドインファイルを設定し、常にTEXTJOIN関数が使用できるようにします。

Excelを開く(新規でも、既存のブックでも可)

「ファイル」タブ → オプション

アドイン → 設定

「Textjoin」にチェックを入れる → OK

保存したアドインファイルが表示されない場合は、「参照」をクリックする。
アドインファイルの中身が表示されるので、該当のファイルを選択し開く。

これでTEXTJOIN関数が使えるようになりましたので、いつも通りに入力して使ってみてください。

対応できない場合の補足

あらかじめ区切り文字をどこかのセルに記入し、それを参照する場合

① =TEXTJOIN(B4,TRUE,A2:J2)
② =TEXTJOIN(A4:B4,TRUE,A2:J2)

結果
① 大根,人参,キャベツ,白菜,もやし,玉ねぎ,きゅうり,トマト,ごぼう,蓮根
② 大根と人参、キャベツと白菜、もやしと玉ねぎ、きゅうりとトマト、ごぼうと蓮根

参照文字列に式を入れる

① =TEXTJOIN(B4,TRUE,OFFSET(A2,0,0,1,10))
② =TEXTJOIN(A4:B4,TRUE,OFFSET(A2,0,0,1,10))

結果
① 大根,人参,キャベツ,白菜,もやし,玉ねぎ,きゅうり,トマト,ごぼう,蓮根
② 大根と人参、キャベツと白菜、もやしと玉ねぎ、きゅうりとトマト、ごぼうと蓮根

これらのように区切り文字にセルを参照したり、配列に数式を入れたりしたものには対応していません。

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

この記事を書いた人

子育てに奮闘しながらも、再びガッツリ走り込める日を夢見るフルタイム会社員。

コメント

コメントする

CAPTCHA


目次
閉じる