Python で Excel のテキストを複数の列に分割する方法

基幹システムや EC サイトの管理画面から商品データを CSV で取り込み、内容が 1 列の表として読み込まれると、「A-101,ノートPC,128000,12」のように、複数の項目が 1 つのセルにまとまってしまうことがあります。このままでは商品コードや単価を項目ごとに並べ替えたり集計したりしにくく、作業のたびに手作業で列を分ける必要があります。

Excel の［データ］タブにある［区切り位置］を使えば、区切り文字を指定して列を分割できます。ただし、同じ形式のファイルが毎日届く場合や、同じ処理を何度も繰り返したい場合には、Python スクリプトで自動化するのが便利です。

本記事では、Spire.XLS for Python を使って、1 列にまとまったテキストを複数の列に分割する方法を紹介します。カンマ区切りの基本から、セミコロン・読点などが混在するデータ、分割した金額を数値セルとして書き込む方法まで、実際の出力を確認しながら解説します。

クイックナビゲーション

• 実行環境とサンプルデータ
• 1 列のテキストを複数列に分ける仕組み
• カンマ区切りの商品リストを分割する
• 区切り文字が複数混在する取引先リスト
• 分割した金額と数量を数値セルにする
• 見出し・罫線・列幅を整える
• まとめ
• FAQ

実行環境とサンプルデータ

本記事のコードは Spire.XLS for Python（動作確認は 16.8.2）と plum-dispatch v1.7.4 で検証しています。Excel 本体は必要なく、次の pip コマンドだけで環境が整います。

pip install Spire.XLS

スクリプトでは、次の 2 行をインポートします。

from spire.xls import *
from spire.xls.common import *

サンプルは、商品リストと取引先リストの 2 つのシートを持つ TextList.xlsx です。商品リストの A 列には「商品コード,商品名,単価,在庫数」がカンマでつながった文字列が入っています。

以降のコードでは、この A 列を読み取り、B 列以降に項目を書き出します。元の A 列は確認用に残しておくため、分割前と分割後を並べて見比べることができます。

1 列のテキストを複数列に分ける仕組み

Excel の［区切り位置］ウィザードでは、対象の列を選び、区切り文字を指定してプレビューを確認しながら列を分けます。Python でも考え方は同じで、次の 3 ステップに分けられます。

1. 取り出す — セルから文字列を取得する
2. 分割する — 区切り文字で split() する
3. 書き込む — 分割した文字列を B 列以降に代入する

それぞれの処理を、Excel の操作と Python のコードで対応させると次のようになります。

CellRange.Text は、セルに入っている文字列を返すプロパティです。分割した結果を文字列として書き込むときは CellRange.Text、金額や数量として計算に使いたいときは CellRange.NumberValue に代入します。

まずは最小限のコードで、1 行分の分割を確認してみます。

text = sheet.Range[2, 1].Text          # 'A-101,ノートPC,128000,12'
fields = text.split(",")               # ['A-101', 'ノートPC', '128000', '12']

for j, value in enumerate(fields, start=2):
    sheet.Range[2, j].Text = value.strip()

enumerate(..., start=2) を使うと、分割した 1 つ目の値を B 列、2 つ目を C 列へと順番に書き込めます。strip() は、カンマの後ろに入った半角スペースを取り除くためのものです。

なお、Worksheet.Range[行, 列] の行・列は 1 始まりです。一方、Worksheet.Columns[i] のインデックスは 0 始まりなので、A 列は Columns[0]、B 列は Columns[1] になります。列幅を指定するときに混同しやすいので注意してください。

カンマ区切りの商品リストを分割する

実際のシート全体を処理するコードは次のとおりです。ここでは 1 行目を元の見出し（商品情報）に使い、2 行目から最終行までをループします。

1. ブックを読み込む
2. 最初のワークシートを取得する
3. B〜E 列に分割後の見出しを書く
4. 2 行目から LastRow までループする
5. A 列の文字列をカンマで分割し、B〜E 列に書き込む
6. 列幅を整えて別名で保存する

from spire.xls import *
from spire.xls.common import *

# Workbook オブジェクトを作成し、ファイルを読み込む
workbook = Workbook()
workbook.LoadFromFile("TextList.xlsx")

# 最初のワークシートを取得
sheet = workbook.Worksheets[0]

# 分割後の見出し
headers = ["商品コード", "商品名", "単価", "在庫数"]
for j, header in enumerate(headers, start=2):
    sheet.Range[1, j].Text = header

# 2 行目から最終行まで、A 列のテキストを分割して B〜E 列に書き込む
for i in range(2, sheet.LastRow + 1):
    text = sheet.Range[i, 1].Text
    fields = text.split(",")
    for j, value in enumerate(fields, start=2):
        sheet.Range[i, j].Text = value.strip()

# 列幅を整える
sheet.AllocatedRange.AutoFitColumns()
sheet.Columns[0].ColumnWidth = 32
sheet.Columns[1].ColumnWidth = 14
sheet.Columns[2].ColumnWidth = 18
sheet.Columns[3].ColumnWidth = 12
sheet.Columns[4].ColumnWidth = 10

# 別名で保存
workbook.SaveToFile("SplitByComma.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

実行すると、A 列の文字列が B〜E 列に分かれ、元の A 列はそのまま残ります。分割結果と元の文字列を見比べられるので、変換が正しく行われたかを確認しやすくなります。

range(2, sheet.LastRow + 1) の +1 は、range() の終端が含まれないためです。見出し行がないシートなら range(1, sheet.LastRow + 1) にします。sheet.LastRow は、データが入っている最終行を返します。

この時点では、単価と在庫数も文字列として書き込まれています。Excel の画面では、数値が文字列として保存されていることを示すエラーチェックの緑の三角が、セルの左上に表示されることがあります（2 枚目の画像）。合計や並べ替えに使う場合は、次の「分割した金額と数量を数値セルにする」の方法を使ってください。

区切り文字が複数混在する取引先リスト

取引先リストのように、データによって区切り文字が異なることもあります。次の例では、セミコロン（;）、カンマ（,）、縦棒（|）、読点（、）が混在しています。

株式会社サクラ;東京都千代田区;03-1234-5678
株式会社アオバ, 大阪府大阪市, 06-2345-6789
株式会社ミドリ|名古屋市中区|052-345-6789
株式会社ホシノ、福岡県福岡市、092-456-7890

このような場合は、Python の re モジュールの re.split() を使い、区切り文字を文字クラスでまとめて指定します。

import re

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("TextList.xlsx")

# 2 番目のワークシート（取引先）を取得
sheet = workbook.Worksheets[1]

headers = ["会社名", "所在地", "電話番号"]
for j, header in enumerate(headers, start=2):
    sheet.Range[1, j].Text = header

for i in range(2, sheet.LastRow + 1):
    text = sheet.Range[i, 1].Text
    fields = [f.strip() for f in re.split(r"[,;|、]+", text)]
    for j, value in enumerate(fields, start=2):
        sheet.Range[i, j].Text = value

sheet.AllocatedRange.AutoFitColumns()
sheet.Columns[0].ColumnWidth = 46
sheet.Columns[1].ColumnWidth = 20
sheet.Columns[2].ColumnWidth = 20
sheet.Columns[3].ColumnWidth = 16

workbook.SaveToFile("SplitMultiDelimiter.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

r"[,;|、]+" の + は「1 文字以上の区切り文字」を意味します。カンマの後に半角スペースが入っていても、各要素に strip() をかけることで前後の空白を落とせます。

区切り文字が 1 種類だけなら str.split() で十分です。空白で区切られたテキストは、引数なしの text.split() を使うと、連続する空白を 1 つの区切りとして扱えます。

fields = text.split()   # 空白（連続する空白も 1 つ）で分割

一方、text.split(",") のように区切り文字を指定した場合は、連続するカンマの間に空の要素が残ります。空欄を詰めたいときは、[f for f in fields if f] で空文字を除いてから書き込むとよいでしょう。

元データが CSV ファイルなら、Python で CSV をリストへ変換する方法 で一度 Python のリストに読み込んでから、必要な列だけを Excel に書き出す構成も取れます。

分割した金額と数量を数値セルにする

分割した単価や在庫数を、Excel 上で数値として扱いたい場合があります。CellRange.Text に代入すると文字列として保存されます。合計や並べ替えに使うときは、CellRange.NumberValue で数値として書き込みます。

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("TextList.xlsx")
sheet = workbook.Worksheets[0]

headers = ["商品コード", "商品名", "単価", "在庫数"]
for j, header in enumerate(headers, start=2):
    sheet.Range[1, j].Text = header

for i in range(2, sheet.LastRow + 1):
    fields = [f.strip() for f in sheet.Range[i, 1].Text.split(",")]

    # 商品コードと商品名は文字列のまま
    sheet.Range[i, 2].Text = fields[0]
    sheet.Range[i, 3].Text = fields[1]

    # 単価と在庫数は数値セルとして書き込む
    sheet.Range[i, 4].NumberValue = int(fields[2])
    sheet.Range[i, 5].NumberValue = int(fields[3])

# 金額と数量の表示形式を設定する
last = sheet.LastRow
sheet.Range[2, 4, last, 4].NumberFormat = "¥#,##0"
sheet.Range[2, 5, last, 5].NumberFormat = "#,##0"

sheet.AllocatedRange.AutoFitColumns()
sheet.Columns[0].ColumnWidth = 32
sheet.Columns[1].ColumnWidth = 14
sheet.Columns[2].ColumnWidth = 18
sheet.Columns[3].ColumnWidth = 12
sheet.Columns[4].ColumnWidth = 10

workbook.SaveToFile("SplitAsNumbers.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

NumberValue に int() で変換した値を渡すと、セルは数値として保存されます。NumberFormat を "¥#,##0" にすると、画面には「¥128,000」のように表示されますが、セルの中身は 128000 の数値のままです。合計や並べ替え、条件付き書式の対象にできます。

商品コードのように先頭の 0 を残したい項目は、CellRange.Text で書き込みます。CellRange.NumberValue は数値として扱うため、001 は 1 になります。項目ごとに「文字列として残すか、数値として扱うか」を決めておくと、あとから集計しやすくなります。

分割した値を Excel の数値や日付に変換する考え方については、Python で Excel のテキストと数値を相互変換する方法 で詳しく解説しています。

見出し・罫線・列幅を整える

最後に、分割後の表を見やすく整えます。列幅は Worksheet.AllocatedRange.AutoFitColumns() でデータに合わせて自動調整し、日本語の見出しが詰まって見える場合は Worksheet.Columns[i].ColumnWidth で微調整します。列番号は 0 始まりなので、A 列は Columns[0]、B 列は Columns[1] です。

見出し行の体裁は CellRange.Style、表全体の罫線は CellRange.Borders で設定できます。ここまでの処理をまとめると、次のようになります。

# 列幅を自動調整し、日本語の見出しに合わせて微調整する
sheet.AllocatedRange.AutoFitColumns()
sheet.Columns[0].ColumnWidth = 32
sheet.Columns[1].ColumnWidth = 14
sheet.Columns[2].ColumnWidth = 18
sheet.Columns[3].ColumnWidth = 12
sheet.Columns[4].ColumnWidth = 10

# 見出し行（B1〜E1）の体裁
head = sheet.Range[1, 2, 1, 5]
head.Style.Color = Color.get_Black()
head.Style.Font.Color = Color.get_White()
head.Style.Font.IsBold = True
head.Style.HorizontalAlignment = HorizontalAlignType.Center

# 表全体（A1〜E7）に細い罫線を引く
used = sheet.Range[1, 1, sheet.LastRow, 5]
for edge in (BordersLineType.EdgeLeft, BordersLineType.EdgeRight,
             BordersLineType.EdgeTop, BordersLineType.EdgeBottom):
    used.Borders[edge].LineStyle = LineStyleType.Thin

記事内の分割結果の画像では、ここで紹介した見出しの体裁と罫線も適用しています。ここまでの処理を関数にまとめておくと、シートが増えても同じ手順で分割できます。分割したデータをいったん Python のリストにまとめてから書き込む場合は、Python のリストを Excel に変換する方法 が参考になります。

まとめ

本記事で扱った内容を整理します。

縦棒（|）も区切りに含める場合は、文字クラスに | を追加します。

1 列にまとまったテキストは、「取り出す → 分割する → 書き込む」の 3 ステップで複数の列に変換できます。元の列を残しておけば、分割結果と見比べながら確認できます。処理を関数化して、定期的に届く CSV の整形に役立ててください。

Spire.XLS for Python の全機能を評価したい場合は、30 日間の無料ライセンスを申請できます。なお無償の試用版では、保存したファイルに評価用の警告シートが追加されます。ライセンスを適用するとこれも出力されなくなります。

FAQ

カンマ以外の区切り文字でも分割できますか？

はい。text.split(";") のように区切り文字を変えられます。複数の区切り文字が混在する場合は、re.split(r"[,;|、]+", text) のように正規表現の文字クラスを使います。区切り文字は、データの形式に合わせて指定してください。

連続する空白を 1 つの区切りとして扱うには？

引数なしの text.split() を使います。"A-101 ノートPC 128000" のように空白が続いても、空の要素を挟まずに分割できます。区切り文字を明示したい場合は text.split(" ") ですが、この場合は連続する空白の間に空の要素が残ります。

分割した金額を合計できる数値にするには？

CellRange.NumberValue に int() または float() で変換した値を代入します。CellRange.Text は文字列として書き込むため、数値計算に使うときは NumberValue を選んでください。表示形式は NumberFormat で設定できます。

商品コードの先頭の 0 を残すには？

CellRange.Text で書き込みます。NumberValue は数値として扱うため、001 は 1 になります。商品コードや電話番号など、桁数を保ちたい項目は文字列のままにしておきましょう。

行によって列数が違う場合はどうすればよいですか？

あらかじめ列数を決めておき、足りない分を空文字で補うと、後続の処理が安定します。

COLUMNS = 4
for i in range(2, sheet.LastRow + 1):
    fields = [f.strip() for f in sheet.Range[i, 1].Text.split(",")]
    fields += [""] * (COLUMNS - len(fields))
    for j, value in enumerate(fields[:COLUMNS], start=2):
        sheet.Range[i, j].Text = value

余分な列がある場合は fields[:COLUMNS] で切り捨てます。列数を固定しておくと、見出し行とデータ行の対応が崩れにくくなります。数値として扱う項目がある場合は、sheet.Range[i, j].Text の代わりに NumberValue を使ってください。

元の列を残したまま、別のシートに分割結果を書き出せますか？

はい。分割結果を書き込むシートを別に用意し、元のシートは読み取り専用として扱います。Worksheets.Add() で分割結果用のシートを追加すると、元のシートをそのまま残せます。

src = workbook.Worksheets[0]
dst = workbook.Worksheets.Add("分割結果")

for i in range(2, src.LastRow + 1):
    fields = [f.strip() for f in src.Range[i, 1].Text.split(",")]
    for j, value in enumerate(fields, start=2):
        dst.Range[i, j].Text = value

workbook.SaveToFile("SplitToNewSheet.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

追加したシートは末尾に作られるため、元のシートの内容と突き合わせながら分割結果を確認できます。シートの追加方法は、Python：Excel ファイルにワークシートを追加する方法 でも詳しく解説しています。