本文へスキップ
スキルアップカレッジ

データ整形の関数——TEXT／TRIM／LEFT・RIGHT・MID／文字列結合

レッスン4：データ整形の関数——TEXT／TRIM／LEFT・RIGHT・MID／文字列結合

このレッスンで学ぶこと

  • TEXT 関数で表示形式を整える発想を持つ
  • TRIM で余計な空白を取る使い方を理解する
  • CLEAN で不可視文字を取る使い方を整理する
  • LEFT／RIGHT／MID で文字列を切り出す発想を持つ
  • &演算子・CONCATENATE・TEXTJOIN で文字列を結合する使い分けを理解する
  • SUBSTITUTE で置換を行う使い方を把握する
  • 業務でよくある「汚いデータ」の整形パターンを身につける

前のレッスンでは、検索の関数（VLOOKUP・XLOOKUP・INDEX/MATCH）の使い分けを扱いました。今回のレッスンでは、視点を変えて「データ整形」を扱います。業務でほかの部署や取引先から受け取ったデータは、必ずどこかしら「汚れて」います。表記が揺れている、余計な空白が入っている、列がバラバラに分割されている——これらを関数で整える発想を、本レッスンで学びます。

業務で出会う「汚いデータ」

業務で関数の前に出会うのは、たいてい次のような「汚いデータ」です。

  • 「東京都新宿区」と「東京都 新宿区」（半角スペースが混入）
  • 「03-1234-5678」と「０３-１２３４-５６７８」（半角全角混在）
  • 「2026/4/1」と「2026年4月1日」と「2026-4-1」（書式バラバラ）
  • 「山田 太郎」と「山田　太郎」（半角全角スペース混在）
  • 別ファイルからコピーした際の「改行」や「タブ」がセル内に残る
  • 1 セルに「氏名 部署 役職」が詰め込まれている
  • 数値の頭にスペースが残って、SUM が動かない

これらをそのまま使うと、検索関数で #N/A が出る、ピボットテーブルで同じ項目が複数に分割される、集計値が合わない、といった事故になります。データ整形の関数は、こうした「汚れ」をクリーニングする道具です。

💡 ポイント データの 60〜70% は他人が作ったものを受け取って使います。「汚いデータ」と出会うのは異常ではなく、業務の標準です。整形の関数を持っていると、出会ったときの動揺が小さくなります。

TEXT 関数——表示形式を整える

TEXT 関数は、数値や日付を「指定した形式の文字列」に変換します。

TEXT の構造

=TEXT(値, 表示形式)

例：

=TEXT(1234567, "#,##0")
… 結果：「1,234,567」（3 桁区切りのカンマ）

=TEXT(0.0832, "0.0%")
… 結果：「8.3%」（パーセント表示・小数点 1 桁）

=TEXT(TODAY(), "yyyy年m月d日")
… 結果：「2026年6月18日」

=TEXT(TODAY(), "yyyy-mm-dd")
… 結果：「2026-06-18」

=TEXT(TODAY(), "aaaa")
… 結果：「木曜日」（曜日を文字列で取得）

=TEXT(TODAY(), "aaa")
… 結果：「木」（短い曜日）

よくある表示形式の記号

記号 意味
0 数値の桁。値がなければ 0 で埋める
# 数値の桁。値がなければ表示しない
, 3 桁区切り
. 小数点
% パーセント表示
yyyy 4 桁の年
m 月（1〜12）
mm 月（01〜12）
d 日（1〜31）
dd 日（01〜31）
aaaa 曜日（日曜日、月曜日……）
aaa 短い曜日（日、月、火……）

TEXT を使う場面

  • 集計結果を「100,000円」のように単位付きで表示する
  • 日付を「2026年6月18日」のように読みやすい形にする
  • パーセントを「85.3%」のように見せる
  • メールの本文に組み込む文字列を整形する

⚠️ 注意 TEXT 関数の結果は「文字列」になります。集計や検索の元データには使わず、表示専用に使うのが基本です。

TRIM——余計な空白を取る

TRIM は、文字列の前後と途中の余計な空白を取り除く関数です。

TRIM の動作

=TRIM("  東京都  新宿区  ")
… 結果：「東京都 新宿区」
… 前後の空白は完全に削除、文字列の途中の連続する空白は 1 つに圧縮

TRIM が解決する典型例

  • 取引先からもらった顧客リストの氏名の前後にスペースが入っている
  • データの末尾にうっかり Enter キーで改行が入った
  • 半角スペース 2 つが続いている

VLOOKUP や XLOOKUP で #N/A が出るときは、TRIM で検索値と検索範囲の両方を整えると、原因が解消することが多いです。

半角と全角は別物

TRIM は半角スペースを処理しますが、全角スペース（　）は標準では削除しません（バージョンや環境によって挙動が違います）。全角スペースを取り除きたい場合は、後述の SUBSTITUTE と組み合わせます。

💡 ポイント TRIM は「データを取り込んだら、まず TRIM を通す」を習慣にすると、検索エラーが大幅に減ります。

CLEAN——不可視の制御文字を取る

CLEAN は、改行・タブ・印刷不能の制御文字を取り除く関数です。

=CLEAN(セル参照)

「Web からコピー＆ペーストしたデータに改行が残ったまま」「他システムから出力したデータにタブが入っている」場面で使います。

TRIM と CLEAN を組み合わせるのが、データ取り込み後の定番処理です。

=TRIM(CLEAN(A2))
… A2 の文字列から制御文字を取り、その後で空白を整える

LEFT・RIGHT・MID——文字列を切り出す

1 つのセルに複数の情報が詰め込まれているとき、関数で切り出します。

LEFT——左から n 文字

=LEFT(文字列, 文字数)

例：

=LEFT("A001-2026", 4)
… 結果：「A001」（左から 4 文字）

RIGHT——右から n 文字

=RIGHT(文字列, 文字数)

例：

=RIGHT("A001-2026", 4)
… 結果：「2026」（右から 4 文字）

MID——指定位置から n 文字

=MID(文字列, 開始位置, 文字数)

例：

=MID("A001-2026-XYZ", 6, 4)
… 結果：「2026」（左から 6 文字目から 4 文字）

LEN——文字数を数える

=LEN("AB001234")
… 結果：8（文字数）

LEN を組み合わせると、「右から 4 文字を除いた残り」のような柔軟な切り出しもできます。

=LEFT(A2, LEN(A2)-4)
… 右から 4 文字を除いた部分

FIND・SEARCH——文字の位置を探す

特定の文字が文字列の何文字目にあるかを返します。

=FIND("-", "A001-2026")
… 結果：5（「-」が 5 文字目にある）

=SEARCH("-", "A001-2026")
… 結果：5（FIND と同じ、ただし大文字小文字を区別しない）

LEFT・MID と組み合わせると、「ハイフンの前まで」「ハイフンの後ろから」のように、文字列の中身に応じた切り出しができます。

=LEFT(A2, FIND("-", A2) - 1)
… A2 の「-」の前まで取り出す

📝 補足 「Excel での文字列処理は手作業」と思われがちですが、LEFT／MID／FIND を組み合わせれば、多くのパターンを自動化できます。マクロを使わなくても、関数だけで十分に対応できる場面が大半です。

文字列の結合

複数のセルや文字を 1 つにつなぐ場面も、業務でよくあります。

& 演算子

最も簡潔な結合方法は、& 演算子です。

=A1 & B1
… A1 と B1 の値を結合

=A1 & " " & B1
… 間にスペースを入れる

="お疲れさまです。" & A1 & "様"
… 文字列とセルを組み合わせる

CONCATENATE（CONCAT）

CONCATENATE 関数も結合に使えますが、& で十分なので使われる場面は減っています。

=CONCATENATE(A1, " ", B1)
… A1 と B1 をスペース区切りで結合

Microsoft 365 では CONCAT 関数（CONCATENATE の後継）も使えます。範囲指定での結合に対応しています。

TEXTJOIN

TEXTJOIN は、複数のセルを「区切り文字」で結合する関数です。範囲指定にも対応しており、& の煩雑さを解消します。

=TEXTJOIN(",", TRUE, A1:A5)
… A1:A5 の値をカンマで結合、空セルは無視
… 結果：「東京,大阪,名古屋,福岡,札幌」のような形

=TEXTJOIN(" / ", FALSE, A1:A5)
… 「 / 」で結合、空セルも含める

第 2 引数の TRUE/FALSE は「空セルを無視するか」を制御します。

💡 ポイント TEXTJOIN は業務で「複数の情報を 1 列に集約してメールに貼る」「アンケートの自由記述を列でつなぐ」場面で大活躍します。Microsoft 365 と Google スプレッドシートの両方で使えます。

SUBSTITUTE——置換する

SUBSTITUTE は、文字列の中の特定の文字を別の文字に置き換える関数です。

=SUBSTITUTE(文字列, 検索文字, 置換文字)
=SUBSTITUTE(文字列, 検索文字, 置換文字, n)

例：

=SUBSTITUTE(A2, " ", "")
… A2 の半角スペースをすべて削除

=SUBSTITUTE(A2, "　", "")
… A2 の全角スペースをすべて削除

=SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "　", "")
… 半角と全角の両方のスペースを削除

=SUBSTITUTE(A2, "-", "/")
… A2 の「-」を「/」に置き換える

=SUBSTITUTE(A2, "東京", "TKY", 1)
… 1 番目の「東京」だけを「TKY」に置き換える（4 番目の引数）

SUBSTITUTE と REPLACE の違い

  • SUBSTITUTE：「この文字を、こちらの文字に置き換える」（文字を指定）
  • REPLACE：「位置 n の文字を、長さ m 分だけ、こちらに置き換える」（位置を指定）

日本語のデータ整形では SUBSTITUTE のほうが圧倒的によく使います。

業務でよくある整形パターン

最後に、業務で繰り返し出会う整形パターンを 5 つ紹介します。

パターン 1：氏名のスペース除去

=SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "　", "")
… 半角・全角スペースをすべて削除

パターン 2：電話番号の正規化

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", "")
… 「-」「(」「)」を削除

パターン 3：取り込みデータのクリーニング

=TRIM(CLEAN(A2))
… 制御文字と余計な空白を取る（取り込み直後の定番）

パターン 4：氏名・部署を別の列に分解

=LEFT(A2, FIND(" ", A2) - 1)
… 「山田 太郎」から「山田」を取り出す

=MID(A2, FIND(" ", A2) + 1, LEN(A2))
… 「山田 太郎」から「太郎」を取り出す

パターン 5：複数列を 1 つに集約してメール本文に

="お客様番号: " & A2 & " / 氏名: " & B2 & " / 申込日: " & TEXT(C2, "yyyy/mm/dd")
… 文字列とセルと TEXT を組み合わせて 1 行にまとめる

これらのパターンを覚えておくと、業務の整形が大きく楽になります。

⚠️ 注意 整形した結果を元データに上書きする場合、必ずバックアップを取ってください。整形は破壊的な操作で、元に戻すのが難しい場合があります。

講師の現場メモ：「日付の取り違えで監査指摘」

私（水田）が生命保険会社のリスク管理にいた頃の話です。月次レポートで、ある契約日のデータが集計から漏れていたことが、四半期決算の段階で発覚しました。原因を追うと、契約日の列に「2026/4/1」と「2026年4月1日」と「2026.4.1」と「2026-04-01」の 4 種類が混在していました。

データを提供してきた他部署が、入力者ごとに違う書式で入れていたのです。VLOOKUP で日付をキーに検索していた集計シートは、書式が違う日付は「別の値」と判断して、一部の契約を漏らしていました。

監査からの指摘を受けて、私は次の整形パイプラインを構築しました。

  1. 取り込み直後に CLEAN と TRIM で制御文字と空白を取る
  2. SUBSTITUTE で「.」「年」「月」「日」を「-」「/」に統一
  3. DATE 関数で文字列を日付に変換
  4. データ検証で「正しい日付以外は入力不可」を設定
  5. 月次のチェック式で「日付の書式バラバラ」を警告

整形パイプラインを動かすようになってから、書式由来のミスはゼロになりました。チーム内では「水田さんの整形パイプライン」と呼ばれ、後輩にも引き継ぎました。

このときに痛感したのは、データ整形の関数は「目立たないが業務リスクを下げる、地味な投資」だ、ということです。新しい関数を覚えるより派手ではないかもしれません。でも、TRIM・CLEAN・SUBSTITUTE・DATE を「習慣として通す」だけで、検索エラー・集計漏れ・監査指摘のリスクが大幅に下がります。本コースでデータ整形を 1 レッスン分割いて扱うのは、この実感があるからです。

まとめ

このレッスンでは、以下のことを学びました。

  • 業務で出会うデータの 60〜70% は他人が作ったもので、汚れているのが標準
  • TEXT 関数：数値や日付を「指定した形式の文字列」に変換する。表示専用で集計には使わない
  • TRIM：文字列の前後と途中の余計な空白を取る。半角スペースが対象、全角は SUBSTITUTE で
  • CLEAN：改行・タブ・印刷不能の制御文字を取る。取り込み直後の定番処理
  • LEFT／RIGHT／MID：文字列の左・右・中間から指定文字数を切り出す
  • FIND／SEARCH：文字の位置を返す。LEFT・MID と組み合わせると柔軟な切り出しができる
  • 文字列結合：& 演算子（最も簡潔）、CONCATENATE／CONCAT、TEXTJOIN（区切り文字付き）
  • SUBSTITUTE：文字を別の文字に置き換える。半角・全角スペース除去、電話番号正規化に使う
  • 業務での典型的な整形パターン 5 つを覚えると、データ整形が大きく楽になる
  • 整形は破壊的な操作。必ずバックアップを取ってから行う

次のレッスンでは、日付と時刻の関数（シリアル値・TODAY・DATE・DATEDIF・EDATE・営業日計算）を扱います。

確認クイズ

このレッスンの理解度をチェックしましょう。