vba テキストから列への変換

このチュートリアルでは、VBAのRange TextToColumnsメソッドを使用して、1つのセル内の文字列を複数の列に変換する方法を紹介します。

テキストから列への変換

レンジVBAのTextToColumnsメソッドは、例えばテキストファイルやcsvファイルからインポートされたデータをクリーンアップするための強力なツールです。 次のようなワークシートを考えてみましょう。

データはすべて1列で、引用符で区切られてExcelに入ってきています。 このデータを列ごとに分割するには、Range TextToColumnsメソッドを使用します。

TextToColumnsの構文

expression.TextToColumns(Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

の式で表されます。

expression

分割したいセルの範囲です。（例：Range(“A1:A23”)）

TextToColumnsメソッドの引数はすべてオプションです。（角括弧で囲まれています）

Destination

結果を置く場所です。データを上書きして、同じ場所で分割することもよくあります。

DataType

使用するテキスト解析のタイプです。xlDelimited（省略時のデフォルト）、またはxlFixedWidthのいずれかになります。

TextQualifier

分割するテキストの各フィールドの周りに引用符（シングルクォーテーションまたはダブルクォーテーション）がある場合、それがシングルかダブルかを示す必要があります。

ConsecutiveDelimiter

TrueまたはFalseで、VBAに同じ区切り記号を2つまとめて1つの区切り記号とみなすように指示します。

Tab

TrueまたはFalseで、デフォルトはFalseです。 これはデータがタブで区切られていることを示します。

Semicolon

TrueまたはFalseで、デフォルトはFalseです。これはデータがセミコロンで区切られていることを示します。

Space

TrueまたはFalseで、デフォルトはFalseです。これはデータがスペースで区切られていることを示します。

Other

TrueまたはFalseで、デフォルトはFalseです。これをTrueに設定した場合、次の引数OtherCharを指定する必要があります。

OtherChar

テキストを区切る文字を指定します。（例：^や|など）。

FieldInfo

分割されるデータの種類に関する情報を含む配列です。 配列の最初の値はデータ中の列番号を表し、2 番目の値は要求するデータ型を表すために使用する定数を表します。

5列でデータ型がテキスト、数字、日付の場合の例としては、以下のようになります。

Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlGeneralFormat), Array(4, xlGeneralFormat), Array(5, xlMDYFormat))

この設定は、以下のように表すこともできます。

Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 3))

2列目の数字は定数の直接数値で表しており、定数xlTextFormatの値は2、xlGeneralFormat（デフォルト）の値は1、xlMDYFormatの値は3になっています。

DecimalSeparator

データ中に数字がある場合に、小数点を表す文字を指定することができます。省略した場合は、システム設定（通常はピリオド）が使用されます。

ThousandSeparator

データ中に数値がある場合に、桁区切り文字を指定することができます。省略した場合は、システム設定（通常はカンマ）が使用されます。

TrailingMinusNumbers

この引数は、マイナス記号が数字の前ではなく後にあることが多い古いシステムで生成されたデータに対する互換性のために用意されています。負の数の後ろにマイナス記号がある場合は、これを True に設定する必要があります。 デフォルトは False です。

テキストから列への変換

以下の手順で、上記のExcelデータを列方向に変換します。

Sub TextToCol1()
   Range("A1:A25").TextToColumns _
   Destination:=Range("A1:A25"), _
   DataType:=xlDelimited, _
   TextQualifier:=xlDoubleQuote, _
   ConsecutiveDelimiter:=True, _
   Tab:=False, _
   Semicolon:=False, _
   Comma:=False, _
   Space:=True, _
   Other:=False, _
   FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
   DecimalSeparator:=".", _
   ThousandsSeparator:=",", _ 
   TrailingMinusNumbers:=True
End Sub

上記のプロシージャでは、すべてのパラメータを入力しました。 しかし、多くのパラメータはfalseに設定されていたり、デフォルトの設定になっていたりして、必要ないものもあります。上記のプロシージャをよりきれいにしたものを以下に示します。どのパラメータを使用しているかを示すために、パラメータ名を使用する必要があります。

Sub TextToCol2()
  Range("A1:A25").TextToColumns _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=True, _
  Space:=True
End Sub

実際に必要なパラメータは4つだけです。データは引用符で区切られ、連続した区切り文字は1つとして扱われ、データはスペースで区切られています。

さらに高速なコード行にするために、パラメータ名を省略することもできますが、その場合はパラメータの場所を保存するためにカンマを入れる必要があります。使用する最後のパラメータまで情報を入れる必要がありますので、この場合には、8番目のパラメータであるデータを区切るスペースのパラメータまでが必要です。

Sub TextToCol3()
   Range("A1:A25").TextToColumns , xlDelimited, xlDoubleQuote, True, , , , True
End Sub

上記のいずれかのプロシージャを実行すると、下の図のようにデータが分割されます。