スプレッドシートでデータ整形する基本｜関数を使った加工方法
スプレッドシートでのデータ整形は、空白をそろえる、文字を分ける、不要な記号を消す、数値として扱える形に直す、この4つを押さえるだけでかなり実務に効きます。
たとえば、フォームの回答一覧、CSVを貼り付けた表、ECの商品リスト、顧客名簿のように「見た目は読めるのに、そのままでは集計しづらい」データはよくあります。こうした場面では、手で直すより関数で整えたほうが早く、再利用もしやすくなります。
- この記事でできること: Google スプレッドシートでよく使う整形関数の基本を、入力例と出力例つきで把握できる
- 向いている場面: CSV貼り付け後の清掃、氏名や住所の分割、表記ゆれの修正、文字列を数値に戻す作業
- 主に使う関数:
TRIM、CLEAN、SUBSTITUTE、SPLIT、REGEXREPLACE、VALUE、ARRAYFORMULA - 対象読者: 関数は少し触ったことがあるが、データ整形の組み立て方がまだ曖昧な人
ここがポイント: データ整形は「1件だけ直す」発想ではなく、列ごとに同じルールを当てる発想に切り替えると一気に楽になります。
前提環境と考え方
本記事は Google スプレッドシート を前提にしています。確認時点は 2026年4月 です。関数名が Excel と共通のものもありますが、正規表現や配列展開の挙動は同じではありません。
整形の基本は、次の順番で考えると崩れにくくなります。
- まず不要な空白や見えない文字を取る
- 次に区切り文字で分割する
- 必要な文字だけ残す、または置換する
- 最後に数値や日付として扱える形へ変換する
この順番にすると、後ろの関数がシンプルになります。逆に、汚れたままの文字列へ先に複雑な式を当てると、途中で崩れやすくなります。
データ整形で最初に覚えたい基本関数
ここでは、現場で出番が多いものだけに絞ります。
1. 空白を整える TRIM
氏名や商品名の前後に空白が混ざると、見た目が同じでも別データとして扱われることがあります。そんなときの基本が TRIM です。
=TRIM(A2)
- 入力例:
" 東京 営業所 " - 出力例:
"東京 営業所"
TRIM は先頭と末尾の空白、そして連続した空白を1つにまとめます。Google のヘルプでも、式や入力規則で空白が意味を持つため重要だと案内されています。
2. 見えない制御文字を消す CLEAN
コピー元が外部システムやPDF由来だと、画面では見えない制御文字が混ざることがあります。並べ替えや検索で妙なズレが出るときは CLEAN を疑います。
=CLEAN(A2)
- 向いている場面: 外部システムから貼り付けたデータ、改行や不可視文字が混じる文字列
- 注意点:
CLEANが消せるのは非表示の ASCII 文字です。全角スペースやノーブレークスペースは別対応が必要です
3. 特定の文字を置き換える SUBSTITUTE
表記ゆれの修正や、記号の統一には SUBSTITUTE が便利です。
=SUBSTITUTE(A2,"株式会社 ","")
- 入力例:
株式会社 サンプル商事 - 出力例:
サンプル商事
電話番号の - を消したい、全角記号を半角へ寄せたい、といった処理でも使いやすい関数です。
実務で使いやすい整形パターン
ここからが本題です。単体の関数名を覚えるより、どう組み合わせるか を見たほうが実務では役立ちます。
パターン1: 氏名や住所を区切って分ける
1つのセルに「姓 名」や「都道府県-市区町村-番地」が入っているなら、まずは SPLIT を使います。
=SPLIT(A2," ")
- 入力例:
山田 太郎 - 出力例: 左のセルに
山田、右のセルに太郎
カンマ区切りのCSV文字列ならこうです。
=SPLIT(A2,",")
SPLIT は、指定した区切り文字ごとにセルを分けます。住所やタグ一覧、カテゴリ列の分解で出番が多い関数です。
パターン2: 余計な記号だけをまとめて消す
文字列の中に、複数種類の不要文字が混ざっているなら REGEXREPLACE が強いです。
=REGEXREPLACE(A2,"[-()]","")
- 入力例:
(03)-1234-5678 - 出力例:
0312345678
電話番号、郵便番号、会員コードの整形では特に便利です。Google スプレッドシートの REGEXREPLACE は RE2 ベースの正規表現を使います。
パターン3: 文字列の数値を計算できる形に戻す
CSV取り込み後の金額列が、見た目は数字でも文字列扱いになっていることがあります。そのままだと SUM や並べ替えで困ります。
=VALUE(A2)
- 入力例:
"1200" - 出力例:
1200（数値）
ただし、通貨記号やカンマが混ざっていると変換しづらいことがあります。その場合は先に置換します。
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"¥",""),",",""))
- 入力例:
¥12,800 - 出力例:
12800
パターン4: 複数行に同じ式を一気に適用する
整形対象が数十行、数百行あるなら、1行ずつコピーするより ARRAYFORMULA を使ったほうが管理しやすくなります。
=ARRAYFORMULA(IF(A2:A="","",TRIM(A2:A)))
この式なら、A列に入力されたテキストをまとめて整形できます。空行まで無理に処理しないので、見た目も崩れにくい形です。
入力例から出力例までまとめて見る
次のような元データを想定します。
A2: 株式会社 サンプル商事
B2: (03)-1234-5678
C2: ¥12,800
D2: 山田 太郎
これを整えるなら、考え方はこうです。
- 会社名:
=TRIM(SUBSTITUTE(A2,"株式会社 ","")) - 電話番号:
=REGEXREPLACE(B2,"[-()]","") - 金額:
=VALUE(SUBSTITUTE(SUBSTITUTE(C2,"¥",""),",","")) - 氏名分割:
=SPLIT(D2," ")
出力のイメージは次の通りです。
- 会社名:
サンプル商事 - 電話番号:
0312345678 - 金額:
12800 - 氏名:
山田/太郎
ここまでできれば、集計、照合、フィルタ、別シート連携がかなり安定します。
よくある失敗と直し方
関数自体より、元データの癖でつまずくことが多いです。
TRIM で空白が消えない
Google のヘルプでも、TRIM ではノーブレークスペースは消えないと案内されています。
対処例:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
見た目は普通の空白でも、別文字として入っているケースがあります。
SPLIT したら意図しない場所で分かれる
区切り文字を複数文字で渡したとき、既定では文字ごとに分割される挙動があります。区切り文字をまとまりで扱いたいときは、第3引数を FALSE にします。
=SPLIT(A2,"--",FALSE)
VALUE がエラーになる
数字の前に通貨記号、後ろに単位、途中に全角カンマなどがあると失敗します。
先に不要な文字を落としてから変換してください。
- 記号除去:
SUBSTITUTE - パターン除去:
REGEXREPLACE - 最後に数値化:
VALUE
実務での使いどころ
関数を覚える目的は、きれいな式を書くことではありません。次の作業へ渡せる形に整えること です。
よくある用途は次の通りです。
- フォーム回答の氏名・部署・電話番号を整えて名簿化する
- CSV貼り付け後の金額列やコード列を集計しやすくする
- ECや在庫表の商品名から不要語を削って検索しやすくする
- 顧客データの表記ゆれをそろえて重複チェックしやすくする
- Apps Script や Looker Studio に渡す前の下処理をシート内で済ませる
「まずシートで整えてから次工程へ流す」形にすると、小さな自動化でも失敗しにくくなります。
関数だけで苦しいときの代替手段
関数は強力ですが、何でも1本の式に詰め込むと読みにくくなります。次の使い分けを意識すると現実的です。
関数向き
- 毎回同じルールで整える
- 行が増えても自動反映したい
- チームがシート上で式を追える状態にしたい
別手段向き
- 変換ルールが多すぎて式が長くなる
- 1回だけ大量データを整形したい
- 複数シートや外部サービスと連携したい
その場合は、Google スプレッドシートの関数だけで抱え込まず、Apps Script、Excel の Power Query、CSV処理用のスクリプトへ分けたほうが保守しやすいことがあります。
まずはこの3本から始める
最初から全部覚える必要はありません。最初の一歩なら、次の3本で十分です。
TRIM: 前後の空白と連続空白を整えるSPLIT: 1セルの情報を列に分けるSUBSTITUTE: 不要な文字や表記ゆれを直す
ここに、必要になった段階で REGEXREPLACE と VALUE を足せば、日常的な整形作業の多くに対応できます。
最後に見るべきなのは、式の美しさではなく、整形後の列がそのまま集計や検索に使えるか です。次にシートを開いた人が迷わない形で残せているか、その点まで含めて整形と考えると失敗しにくくなります。