スプレッドシートで大量のデータを扱っていると、文字列の抽出や結合、さらには分割や置換といった作業が必要になることがよくありますよね。例えば、不要な改行を一括で消したいときや、特定の条件でセルを検索してカウントしたいときなど、手作業で行うには膨大な時間がかかってしまうかなと思います。私自身、日々のデータ整理の中でこういった問題に直面し、もっと効率的に処理できないかと模索してきました。特に正規表現を使った高度な処理を覚えると、これまでの苦労が嘘のようにデータクレンジングがスムーズに進むようになります。この記事では、スプレッドシートの文字列に関する様々な悩みを解決するための実践的なテクニックを、基本から応用までまとめてご紹介します。
- 基本的な文字列の抽出や結合方法
- 複雑な条件で文字列を分割や置換するテクニック
- 正規表現を活用した高度なデータ処理の仕組み
- 業務の効率化に繋がる文字列操作の実践的なコツ
スプレッドシートの文字列を自在に扱う
まずは、スプレッドシートの文字列を思い通りに扱うための基本テクニックから見ていきましょう。日々の業務で特に使う頻度の高い抽出、結合、分割、そして不要な文字の削除について、実例を交えながら分かりやすく解説していきますね。
特定の文字列を抽出する基本関数
大量のテキストデータから必要な部分だけを取り出す「抽出」は、データ整理の基本中の基本ですね。文字数や位置が決まっている場合は、とてもシンプルな関数で対応できます。
代表的なものとして、左端から抽出するLEFT関数、右端から抽出するRIGHT関数、そして途中から抽出するMID関数があります。たとえば、「東京都港区」というデータから「東京都」だけを取り出したい場合は、左から3文字を指定するLEFT関数が便利かなと思います。
| 関数名 | 使い方と特徴 | 使用例 |
|---|---|---|
| LEFT関数 | 文字列の左端から指定した文字数を抽出します。 | =LEFT(“東京都港区”, 3) → 東京都 |
| RIGHT関数 | 文字列の右端から指定した文字数を抽出します。 | =RIGHT(“03-1234-5678”, 4) → 5678 |
| MID関数 | 指定した開始位置から任意の文字数を抽出します。 | =MID(“2024年01月15日”, 6, 2) → 01 |
ただ、メールアドレスからユーザー名（@より前の部分）だけを取り出したいときのように、文字数がデータによってバラバラなことも多いですよね。そんな時は、特定の文字の位置を探し出すFIND関数やSEARCH関数を組み合わせるのがおすすめです。
FIND関数とSEARCH関数の違い
FIND関数は「大文字と小文字を厳密に区別する」のに対し、SEARCH関数は「区別せず、ワイルドカード（*や?）も使える」という違いがあります。目的に合わせて使い分けてみてくださいね。
たとえば、A2セルにある「user@example.com」から「user」を取り出したいなら、=LEFT(A2, FIND(“@”, A2) – 1)のように数式を組み合わせます。少し複雑に見えますが、FINDで見つけた位置から1文字引いた分だけLEFTで切り取る、という仕組みですね。
複数の文字列を一つに結合する方法
別々のセルに入っている苗字と名前を繋げたり、住所のパーツを一つにまとめたりしたい場面もよくあるかなと思います。文字列を結合する方法はいくつかあるので、状況によって使いやすいものを選んでみてください。
一番手軽なのは、「&（アンパサンド）」を使う方法です。「=A1 & B1」のように書くだけで、直感的に文字列をくっつけることができます。間にスペースを入れたい場合は、「=A1 & ” ” & B1」のようにすればOKです。
もっとたくさんのセルを繋ぎたい場合は、関数を使うのが便利ですね。
- CONCATENATE関数：複数の文字列やセルを順番に繋ぎます。フォーマットが決まっているテキストを作るときに向いています。
- TEXTJOIN関数：これが本当に強力です！区切り文字（カンマなど）を指定して範囲を繋げるだけでなく、空白セルを自動的に無視（スキップ）してくれる機能があります。
改行を挟んで結合する裏技
セルの中で改行しながらデータを結合したい場合は、区切り文字として「CHAR(10)」を指定してみてください。「=TEXTJOIN(CHAR(10), TRUE, A2:D2)」のように書き、セルの「テキストの折り返し」設定を有効にすると、綺麗に複数行のリストが出来上がりますよ。
区切り文字で文字列を分割する
結合の反対で、一つのセルに入っているデータを複数のセルに分けたいときは、SPLIT関数が大活躍します。CSV形式で貼り付けたデータや、スペース区切りの姓名などを展開したいときに必須の関数ですね。
基本的には「=SPLIT(テキスト, 区切り文字)」と入力するだけで、区切り文字を基準にして右隣のセルへデータが分割（スピル）されていきます。
単語で分割する時の注意点
たとえば「 and 」という単語を区切り文字にしたい場合、そのままでは「a」「n」「d」それぞれの文字でバラバラに分割されてしまいます。これを防ぐには、SPLIT関数の第3引数（各文字での分割）を「FALSE」に設定する必要があります。「=SPLIT(A1, ” and “, FALSE)」と書くことで、意図通りの分割ができますよ。
また、先ほどの結合の時と同じように、セル内の改行で分割したい場合は、区切り文字に「CHAR(10)」を指定すれば、改行ごとに別のセルへデータを分けることができます。
セル内の改行を一括で削除する
他からコピーしてきたデータに不要な改行がたくさん含まれていて、表が縦に間延びしてしまった経験はありませんか？関数を使わずに、手っ取り早くシート上の改行を消したい場合は、「検索と置換」機能を使うのが最もスピーディーです。
ショートカットの「Ctrl + H」（MacならCommand + Shift + H）で検索と置換の画面を開き、「正規表現を使用した検索」にチェックを入れます。そして、検索欄に改行を表すコードである「\n」を入力し、置換後を空白のまま「すべて置換」を実行してみてください。これだけで、シート内の不要な改行が一瞬で消え去ります。
もし、改行をカンマ区切りに変換したい場合は、置換後の入力欄に「,」を指定すれば大丈夫です。この方法はデータ整理の時間を大幅に短縮してくれるので、ぜひ覚えておいてほしいテクニックですね。
不要な空白や制御文字を削除する
システムから書き出したデータには、目で見ても分かりにくい不要なスペースや制御文字が混ざっていることがよくあります。これが原因でうまく集計できないこともあるため、データクレンジング（お掃除）の作業はとても重要です。
まず、先頭や末尾にある余分なスペースを消し、単語間の連続するスペースを一つにまとめてくれるのがTRIM関数です。スプレッドシートのTRIM関数は全角スペースにも対応しているので、日本語のデータでも安心ですね。
次に、印刷できないような制御文字を取り除くのがCLEAN関数です。
実務でのベストプラクティス
実務でデータを綺麗にする際は、「=TRIM(CLEAN(A1))」のように、この2つの関数を組み合わせて使うのが一般的で確実な方法です。文字の間にあるスペースを完全に一つ残らず消したい場合は、「=SUBSTITUTE(A1, ” “, “”)」を併用すると完璧かなと思います。
スプレッドシートの文字列操作を極める
ここからは、もう一歩踏み込んだ高度な文字列操作について解説していきますね。単純な処理では対応しきれない複雑な条件や、大量のデータを一括で整えたい場面で非常に役立つ知識ばかりです。
関数で特定の文字列を置換する
元のデータを残したまま、別の列でクリーンなデータを作りたいときは、置換を行う関数を使用します。
定番なのはSUBSTITUTE関数です。「=SUBSTITUTE(対象テキスト, 検索文字列, 置換文字列)」という形で書き、指定した特定の文字を別の文字に置き換えます。たとえば株式会社を(株)に直したいときなどに便利ですね。また、「2番目に出てきた文字だけを置換する」といった細かい指定も可能です。
もう一つの強力なツールがREGEXREPLACE関数です。こちらは「正規表現」と呼ばれるパターンを使って、柔軟にデータを置換することができます。たとえば、電話番号のハイフンの位置がバラバラなデータを、統一されたフォーマットに一括で直すような魔法みたいな処理ができるんです。
一括置換の革新的なテクニック
複数の違う単語をいっぺんに置換したい場合、昔はSUBSTITUTE関数を何度も重ねて書く必要があり、数式がとても長くなっていました。しかし最近では、REDUCE関数とLAMBDA関数という新しい関数を組み合わせることで、変換用の表（マスター）を用意するだけで、複数条件の置換を自動でループ処理させることができるようになりました。かなり上級者向けの技ですが、作業を劇的に減らせる画期的な手法です。
正規表現で高度なデータ処理を行う
スプレッドシートの最大の強みとも言えるのが、この正規表現（RE2構文）を標準関数でしっかりサポートしている点かなと思います。正規表現を活用するためのREGEXEXTRACT関数を使えば、普通の関数では難しいようなパターンの抽出も可能になります。
たとえば、「墨付き括弧【】の中の文字だけを取り出したい」といった場合や、「いくつかのキーワードのどれかが含まれていたら抽出する」といった条件ですね。
少し実用的な例を挙げると、日本の住所から「都道府県名」だけを正確に取り出す処理です。都道府県は「東京都」など3文字のものと、「神奈川県」など4文字のものが混ざっているので、単純な文字数指定では処理が複雑になってしまいます。
これを正規表現で書くと、=REGEXEXTRACT(A2, “^(.+?[都道府県])”)のようになります。行の先頭から始まり、「都・道・府・県」のどれかの文字が出るまでを最短でマッチさせる、という指示です。これなら文字数に関係なく、バッチリ都道府県だけを抜き出すことができますよ。
指定した文字列を含むセルを検索
データベースの中から、特定のキーワードが含まれているセルを探し出して集計したり、フラグを立てたりする作業も多いですよね。
まずは定番のCOUNTIF関数です。ワイルドカード（*や?）を使うことで、「〜を含む」といった部分一致の検索ができます。
- 「株式会社」を含む： =COUNTIF(A:A, “*株式会社*”)
- 「東京都」で始まる： =COUNTIF(A:A, “東京都*”)
もし、「東京」または「大阪」を含むというOR条件で探したい場合は、COUNTIF関数を足し算（+）で繋ぐのが手軽な方法です。
さらに高度な検索には、SQLのような構文が使えるQUERY関数が圧倒的におすすめです。条件を指定するWHERE句の中で、文字列が含まれているかを判定する「CONTAINS」や、より詳細なパターンを指定できる「LIKE」といった演算子を使うことで、複雑な抽出を1つの数式で実現できます。
また、「このキーワードが含まれているか？」という真偽（TRUE/FALSE）だけを判定したい場合は、先ほど登場した正規表現の仲間であるREGEXMATCH関数を使うと、数式がとてもすっきりしますよ。
文字列の数や出現回数をカウント
入力された文字数が規定内に収まっているかチェックしたりする際に使うのが、長さをカウントする関数です。
基本となるLEN関数は、半角も全角もスペースも、すべて「1文字」としてカウントします。一方、LENB関数は文字を「バイト数」で計算します。半角は1バイト、ひらがなや漢字は2バイトとして数えるため、古いシステムへのデータ連携時や、特定の文字数制限をチェックしたい時に使われますね。
特定の文字が何回出てきたか数える方法
「セルの中に特定の単語が何回書かれているか」を直接数える専用関数はないのですが、ちょっとした頭の体操で計算できます。元の文字数から、「その単語を空っぽに置換した状態の文字数」を引き算し、それを探したい単語の文字数で割るという方法です。パズルのようですが、とても実用的な計算式ですよ。
他にも、見た目は数字でもスプレッドシート上では「文字列」として扱われていて計算エラー（#VALUE!）になることがあります。そんな時はVALUE関数を使って数値に変換したり、逆に数値を文字列として扱いたい時はTEXT関数を使ってフォーマットを指定したりと、データ型の変換も文字列操作の重要な一部になります。
スプレッドシートの文字列操作まとめ
いかがでしたでしょうか。今回は、スプレッドシートの文字列を扱うための基本から、正規表現を使った高度なテクニックまで幅広くご紹介しました。
文字列の抽出、結合、分割、置換といった操作をマスターすることは、単なる作業の時短だけでなく、データの質を高めることにも直結します。特に、検索と置換を使った改行の一括削除や、関数を組み合わせたデータクレンジングは、知っているか知らないかで業務効率に雲泥の差が出ますよね。私自身、これらの関数を使いこなせるようになってから、データ整理に対する苦手意識がすっかり無くなりました。
注意点と免責事項
本記事で紹介した数式や正規表現はあくまで一般的な目安であり、データの状態（特殊な制御文字や環境依存文字の混入など）によっては意図した通りに動作しない場合があります。大規模なデータベースを操作する前には、必ずバックアップを取り、一部のデータでテストを行うことをお勧めします。また、機能のアップデートにより関数の動作が変わる可能性もあるため、正確な仕様や最新情報については必ず公式サイトをご確認ください。最終的なデータの取り扱いに関する判断は、専門家にご相談いただくなど自己責任でお願いいたします。
スプレッドシートの関数はパズルのような面白さがあります。ぜひ、ご自身の実務データに合わせて色々と組み合わせて試してみてくださいね。この記事が、皆さんの日々のデータ処理の悩みを解決する一助になれば嬉しいです。
