SkillStack Lab 運営者の「スタック」です。
システムから出力したCSVやログデータをExcelへ取り込み、そのたびに「区切り位置」を設定していませんか。
1回だけなら手作業でも問題ありませんが、毎日・毎月同じ操作を繰り返しているなら、自動化を検討する価値があります。
結論から言うと、セルの文字を動的に分割するならTEXTSPLIT、定期的なCSVならPower Query、Excel操作そのものを自動化するならVBAと使い分けるのが分かりやすいです。
私自身も以前は、何千行ものデータを前にして、同じ手順を繰り返す作業に時間を取られていました。
スタック毎回同じCSVを開いて、同じ場所で区切って、同じ形に整える。このような繰り返し作業は、自動化を考えるきっかけになります。
この記事では、「エクセルの区切り位置を自動化したい」という方へ、TEXTSPLIT関数、Power Query、VBAの使い分けから、先頭の0が消える問題、CSV取り込み時の注意点まで解説します。
- 区切り位置を自動化する3つの方法
- TEXTSPLIT関数で文字列を自動分割する方法
- Power QueryでCSVの分割手順を再利用する方法
- VBAのTextToColumnsで区切り位置を実行する方法
- 社員番号などの先頭の0を消さない方法
エクセルの区切り位置を自動化する3つの方法
区切り位置を自動化する方法は、大きく分けると次の3つです。
| 方法 | 向いている作業 | 特徴 | 難易度 |
|---|---|---|---|
| TEXTSPLIT | セル内の文字分割 | 元データの変更に合わせて再計算 | 低 |
| Power Query | 定期的なCSV・大量データの整形 | 一度作った変換手順を再利用 | 低〜中 |
| VBA | Excel操作を含む定型処理 | TextToColumnsなどの操作をコード化 | 中〜高 |
迷った場合は、次の基準で考えてみてください。
- セルの内容が変わるたびに結果も変えたい → TEXTSPLIT
- 毎月同じ形式のCSVを取り込みたい → Power Query
- 分割以外のExcel操作もまとめて自動化したい → VBA
Excel全体の自動化手段を比較したい場合は、Excel自動化の方法を用途別に比較した記事も参考にしてください。
TEXTSPLIT関数で区切り位置を自動化する
Microsoft 365またはExcel 2024を使っているなら、区切り文字によるセル分割にはTEXTSPLIT関数が便利です。
TEXTSPLITはExcel 2021の関数ではありません。Microsoftの現行サポート情報では、Excel for Microsoft 365とExcel 2024などが対象です。
例えばA2セルに次の文字列が入っているとします。
東京,営業部,田中
カンマで分割するなら、次の数式を入力します。
=TEXTSPLIT(A2,",")
数式を入力したセルから右方向へ結果が展開されます。
複数の区切り文字にも対応できる
TEXTSPLITは複数の区切り文字も指定できます。
例えば「カンマ」と「半角スペース」のどちらでも分割するなら、次のように配列定数を指定します。
=TEXTSPLIT(A2,{","," "})
連続する区切り文字によって空白セルを作りたくない場合は、ignore_empty引数も利用できます。
=TEXTSPLIT(A2,{","," "},,TRUE)
TEXTSPLITが使えないExcelでは従来関数を組み合わせる
TEXTSPLITが利用できない環境では、LEFT・MID・FIND・LENなどを組み合わせる方法があります。
A2セルに「田中 太郎」のように姓と名が半角スペースで区切られている場合は、例えば次のように分割できます。
姓：
=LEFT(A2,FIND(" ",A2)-1)
名：
=MID(A2,FIND(" ",A2)+1,LEN(A2))
ただし、区切り文字が複数種類ある、分割数が多い、といったデータでは数式が読みにくくなります。その場合はPower Queryも検討してください。
Power QueryならCSVの区切り処理を繰り返し使える
毎月・毎週のように同じ形式のCSVを取り込んでいるなら、Power Queryは有力な選択肢です。
Power Queryでは、外部データの取り込みや列の分割、不要列の削除、データ型の変更などを「適用したステップ」として保存できます。
Windows版Excelでは、Excel 2016以降の「データの取得と変換」としてPower Queryを利用できます。
Power Queryで区切り文字を指定する手順
- ［データ］タブを開く
- ［テキスト/CSVから］を選択する
- 対象のCSVファイルを選択する
- ［データの変換］を選んでPower Queryエディターを開く
- 分割したい列を選択する
- ［列の分割］→［区切り記号による分割］を選択する
- カンマ・スペース・タブなどの区切り記号を指定する
- ［閉じて読み込む］でExcelへ戻す
同じデータソースを使う場合は、次回以降にクエリを更新することで、保存した変換手順を再実行できます。
ファイル名や保存場所、列構成など元データの条件を大きく変えると、既存クエリをそのまま更新できないことがあります。自動化では「入力データの形式をそろえる」ことも重要です。
Power Queryの基本操作をさらに詳しく知りたい方は、Power Queryの使い方を実務ベースで解説した記事も参考にしてください。
VBAのTextToColumnsで区切り位置を実行する
Excel上のボタンから処理したい、分割後に別の処理も続けたい、といった場合はVBAが選択肢になります。
Excel VBAには、区切り位置の処理を実行するRange.TextToColumnsメソッドが用意されています。
例えばA2:A1000にカンマ区切りの文字列が入っている場合は、次のように記述できます。
Sub SplitByComma()
With Worksheets("Sheet1").Range("A2:A1000")
.TextToColumns _
Destination:=.Cells(1, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
Comma:=True
End With
End Subこの例では、A列にある文字列をカンマで分割します。
分割結果は右側の列へ展開されます。既存データを上書きしないよう、実行前に出力先を確認し、重要なブックではバックアップを取ってから試してください。
先頭の0を残すならFieldInfoで文字列を指定する
社員番号や商品コードの「00123」のように、先頭の0自体に意味があるデータは注意が必要です。
Excelが数値として解釈すると、00123が123になる場合があります。
TextToColumnsでは、FieldInfoを利用して出力列を文字列として解析できます。
FieldInfo:=Array( _
Array(1, xlTextFormat), _
Array(2, xlGeneralFormat) _
)xlTextFormatはテキスト形式を意味します。どの列を文字列として扱うかは、実際のCSV構成に合わせて設定してください。
VBAを使うべきか迷っている場合は、VBAとPower Query・Office Scripts・Pythonなどの使い分けも確認してみてください。
区切り位置で先頭の0が消えるときの対処法
区切り位置で特に注意したいのが、社員番号・商品コード・電話番号などの先頭ゼロです。
例えば、
00123
を数値として取り込むと、
123
になることがあります。
手動の区切り位置では列のデータ形式を文字列にする
従来の「区切り位置」ウィザードを利用する場合は、最後の画面で対象列を選択し、列のデータ形式を「文字列」に指定します。
コードや社員番号のように計算する必要がない値は、数値ではなく文字列として管理する方が適している場合があります。
CSVなら「テキスト/CSVから」で取り込む
CSVをダブルクリックして開くと、Excelは現在の既定設定を使って各列のデータを解釈します。
そのため、先頭ゼロや日付形式を自分で管理したいCSVでは、ファイルを直接開くのではなく、Excelの［データ］から取り込む方法が便利です。
- Excelで新しいブックを開く
- ［データ］を選ぶ
- ［テキスト/CSVから］を選ぶ
- 対象ファイルを選択する
- 区切り記号やデータの状態をプレビューで確認する
- 必要なら［データの変換］で型を設定する
毎回同じCSVを取り込むなら、手動の「区切り位置」を繰り返すより、Power Queryで取り込み手順自体を保存する方法を検討しましょう。
分割される列数が毎回変わる場合はどうする？
「先月は3項目だったのに、今月は5項目ある」というように、区切り文字の数が変わるデータでは方法選びに注意します。
TEXTSPLITは結果に合わせてスピルする
TEXTSPLITなら、区切られた結果の個数に合わせて隣のセルへ展開されます。
ただし、展開先のセルに別のデータが入っていると、スピルできずエラーになるため、結果を表示する周辺は空けておきます。
Power Queryは出力列数の変化を確認する
Power Queryの列分割では、作成されたクエリが想定する列数を確認してください。
M言語のTable.SplitColumnでは、新しく作る列名や列数、余分な値をどう扱うかを指定できます。標準の処理では、想定した列数を超える値がある場合の扱いに注意が必要です。
分割数が頻繁に変わるデータなら、「列を増やし続ける」設計が本当に必要かも確認してください。データによってはPower Queryで列ではなく行へ分割した方が後工程を作りやすい場合があります。
住所の自動分割は単純な区切り位置では難しい
住所や氏名のように、必ずしも決まった区切り文字が入っていないデータは注意が必要です。
例えば住所を「都・道・府・県」で単純にTEXTSPLITすると、「京都府」のように途中にも対象文字が含まれる住所を正しく処理できません。
=TEXTSPLIT(A2,{"都","道","府","県"})のような式だけで、日本全国の住所を都道府県とそれ以降へ正確に分割しようとしないでください。
住所を安定して分割するなら、都道府県マスターとの照合や、元システム側で住所項目を分けて出力できないかを検討する方が安全です。
一方、「東京都新宿区1-2-3」の文字部分と数字部分を分けたいなど、文字種の変化に規則性がある場合は、Power Queryの「数字から数字以外」「数字以外から数字」などの分割方法が使える場合があります。
区切り位置を手作業で使うならキーボード操作も活用する
年に数回しか行わない作業まで、無理にPower QueryやVBAへ置き換える必要はありません。
Windows版Excelでは、Altキーを押すとリボン上にキーヒントが表示されます。
Microsoftのキーボードショートカットでは、Alt + Aで［データ］タブを開けます。その後は画面に表示されたキーヒントを使って［区切り位置］を選択できます。
単発処理なら、このような操作で十分な場合もあります。
エクセルの区切り位置自動化でよくある質問
まとめ｜繰り返す区切り位置はPower Queryも検討しよう
Excelの区切り位置を自動化するときは、単に「一番高度な方法」を選ぶ必要はありません。
- 単発の作業 → 手動の区切り位置
- セルの文字を動的に分割 → TEXTSPLIT
- 定期的なCSVの取り込み・整形 → Power Query
- Excel操作までまとめて実行 → VBA
特に、毎月同じCSVを開いて、同じ列を分割し、同じ形式へ整えているなら、Power Queryとの相性がよい作業です。
最初に一度だけ取り込みと変換手順を作っておけば、その後は同じ処理を更新によって再利用できます。
毎月のCSV加工を減らしたい方は、実務での使い方を一通り確認してみてください。
