セル区切りになっている数値・文字を「｜」「,」区切りのデータに変換する【前処理小ネタ】

Excel、スプレッドシートのセル区切りから正規表現のような「｜」「,」区切りのデータに変更するにはどうするか。今回は全然数値の話ではないけれど質問箱で多いので簡略的に説明することとする。最初に正規表現でキーごとにデータを取得したいときに面倒だったのでまとめてみました。

流れ

Excel、スプレッドシートのセル区切りから正規表現に使える「｜」「,」区切りのデータに変更するにはどうするか。

とりあえず例を用意してみました。A列に書いているのは意味のない5桁の数字です。これを右のように「|」区切りにしていきます。

Excelで「=A1&”|”」「=A1&”,”」のように区切りたい記号で区切ります。

そのままだと使いにくいのでB列をコピーして「右クリック>特殊貼り付け>値のみ貼り付け」します。

今度は横につなげていきたいので縦横逆に貼り付けを行います。「右クリック>特殊貼り付け>転置のみ貼り付け」を行います。

こんな感じですね。

「右クリック>書式なしで貼り付け」を行います。たぶん貼り付けるとこういう形になりますね。このままだと空白が入ってしまうので空白をなくします。

Ctrl+Hで検索と置換を行い、間に何もないように置換をしていきます。今回は「|」で区切っているので「|」と空白部分をまとめてコピーし、検索します。検索したところは緑に変わるので、「すべて置換」を行い空白を消します。

このような形になっていればOKです。これをデータ取得する際のSQLとかに使うと早く作業が進むのではないでしょうか。作業は面倒なので手作業でやったほうが速いとは思います。

ただ、10~50くらいなら時間はかかりませんが、1000や10000くらいのデータを引き出す時はこのほうが楽・正確なので適宜使い分けてみてください。