Excel関数で文字列を抽出する方法をお探しですね。

広告

Excelで文字列をうまく扱うコツ：抽出・結合・置換・分割を使いこなそう

Excelで名簿や住所、メールアドレスなどを扱っていると、「名前の苗字だけ取り出したい」「姓と名をくっつけたい」「古い部署名をまとめて変えたい」といった作業がよく出てきますよね。

1つ2つなら手作業でもなんとかなりますが、100件、1000件となるとミスも増えるし、時間もかかります。

この記事では、Excelの文字列操作でよく使う関数を、実務で役立つ形にまとめて紹介します。

まずは基本を押さえよう：文字列操作って何をするの?

Excelの文字列操作は、大きく分けると**「抽出」「結合」「置換」「分割」「整形」**の5つに分けられます。

関数の種類がたくさんあって最初は戸惑うかもしれませんが、「自分が今やりたいことはどれかな?」と考えると、使う関数が見つけやすくなります。

たとえば、こんな感じです。

– 商品コードの最初の3文字だけ取り出したい → **抽出**
– 姓と名を1つのセルにまとめたい → **結合**
– 「営業1課」を「営業部」に変えたい → **置換**
– カンマ区切りのデータを列に分けたい → **分割**

文字列操作でよく使う基本的な関数には、文字数を数える**LEN関数**、余分な空白を消す**TRIM関数**、見えない変な文字を取り除く**CLEAN関数**などがあります。

地味な関数ですが、これらを使わないと「なぜか検索できない」「関数がうまく動かない」といったトラブルの原因になります。

たとえば、見た目は同じ「東京」でも、片方の末尾に半角スペースが入っていると、Excelは別の文字列として扱います。

関数が思い通りに動かないときは、「見えない空白や文字が混ざってないかな?」と疑ってみるのが大事です。

もう1つ注意したいのが、**「数値」と「文字列」の違い**です。

見た目は数字でも、Excelが文字列として保存している場合があります。

たとえば社員番号「00123」は、数値として扱うと先頭のゼロが消えて「123」になってしまいます。

郵便番号や電話番号、IDのように計算しない数字は、文字列として扱うことを意識しておくとトラブルを防げます。

実務では、1つの関数だけで済むこともあれば、複数の関数を組み合わせることもあります。

たとえば「ハイフンの前だけ取り出す」「メールアドレスの@より後ろを取り出す」といった処理では、位置を調べる**FIND関数**と、文字を抜き出す**LEFT関数**や**MID関数**を組み合わせます。

Microsoft 365を使っている人は、**TEXTBEFORE関数**や**TEXTAFTER関数**といった新しい関数も使えます。

これらは式が短くて分かりやすいのがメリットです。

ただし、古いバージョンのExcelでは使えないので、ファイルを共有する相手の環境も確認しておくと安心です。

文字列を取り出す：LEFT・RIGHT・MIDと検索関数の使い分け

文字列を取り出すときに一番よく使うのが、**LEFT関数**、**RIGHT関数**、**MID関数**です。

– **LEFT関数**：左から指定した文字数を取り出す
– **RIGHT関数**：右から指定した文字数を取り出す
– **MID関数**：指定した位置から指定した文字数を取り出す

たとえば、A2セルに「ABC-12345-TOKYO」と入っている場合、

– `=LEFT(A2,3)` → 「ABC」
– `=RIGHT(A2,5)` → 「TOKYO」

という感じです。

文字数や位置が決まっているデータなら、この3つだけでもかなりの作業に対応できます。

ただ、実際のデータは「いつも左から3文字」のようにきれいにそろっているとは限りません。

商品名の長さが違ったり、ハイフンの位置が行によって違ったりします。

そんなときは、**FIND関数**や**SEARCH関数**で特定の文字がどこにあるかを調べてから取り出します。

– **FIND関数**：大文字と小文字を区別する
– **SEARCH関数**：大文字と小文字を区別しない

日本語のデータではあまり差が出ませんが、英数字を含むコードやメールアドレスでは使い分けると安全です。

たとえば、A2セルに「営業部-山田太郎」と入っていて、ハイフンより前の部署名だけ取り出したいときは、

“`
=LEFT(A2,FIND(“-“,A2)-1)
“`

と書きます。

FIND関数でハイフンの位置を調べて、その1文字前までをLEFT関数で取り出す考え方です。

逆に、ハイフンより後ろを取り出すなら、

“`
=MID(A2,FIND(“-“,A2)+1,LEN(A2))
“`

のように、ハイフンの次の位置から文字列の最後までを抜き出します。

LEN関数で全体の文字数を指定しておけば、後ろ側の長さが行ごとに違っても大丈夫です。

Microsoft 365では、もっと分かりやすい**TEXTBEFORE関数**と**TEXTAFTER関数**が使えます。

– ハイフンの前 → `=TEXTBEFORE(A2,”-“)`
– ハイフンの後 → `=TEXTAFTER(A2,”-“)`

従来のLEFT・MID・FINDの組み合わせより式が短くなって、あとで見返したときにも意味が分かりやすいのがメリットです。

ただし、古いExcelでは使えないので注意してください。

大量のデータを扱う場合は、**Power Query**を使う方法もあります。

Power Queryは関数をセルに入力するのではなく、操作手順として記録できるので、毎月同じ形式のデータを取り込むような作業に向いています。

結合・置換・整形でデータを使いやすくする

文字列を結合する

文字列をつなげるには、**&記号**、**CONCAT関数**、**TEXTJOIN関数**がよく使われます。

一番手軽なのは`&`でつなぐ方法です。

“`
=A2&B2
“`

姓と名の間にスペースを入れたいときは、

“`
=A2&” “&B2
“`

のように、間に文字列を追加します。

**TEXTJOIN関数**は、複数のセルを指定した区切り文字でつなげるときに便利です。

たとえば、住所の都道府県、市区町村、番地が別々のセルに入っている場合、

“`
=TEXTJOIN(“”,TRUE,A2:C2)
“`

とすれば、空白セルを無視して結合できます。

カンマ区切りにしたいときは、区切り文字に`,`を指定します。

空白セルを無視できるので、入力が不完全な名簿やアンケート結果でも余計な区切り文字が出にくくなります。

文字列を置き換える

置換では、**SUBSTITUTE関数**と**REPLACE関数**を使い分けます。

– **SUBSTITUTE関数**：指定した文字列を別の文字列に置き換える
– **REPLACE関数**：何文字目から何文字分を置き換えるか位置で指定

たとえば、

“`
=SUBSTITUTE(A2,”株式会社”,”(株)”)
“`

とすれば、セル内の「株式会社」を「(株)」に置き換えられます。

文字そのものを探して置き換えるならSUBSTITUTE、決まった位置の文字を差し替えるならREPLACEと覚えておくと分かりやすいです。

文字列を整える

文字列を整える処理も、データ加工では欠かせません。

– **TRIM関数**：前後や単語間の余分なスペースを整理
– **CLEAN関数**：改行コードなどの見えない文字を取り除く
– **UPPER関数/LOWER関数**：英字を大文字・小文字に統一
– **ASC関数/JIS関数**：半角・全角を変換

表記がバラバラだと、集計や検索で同じものが別物として扱われてしまいます。

置換や整形は見た目を整えるだけでなく、分析の精度を上げるための準備でもあるんです。

特に名寄せやマスタ整備では、複数の置換をまとめて行いたい場面があります。

置換対象が多い場合は、Excel関数だけだと式が長くなって管理が大変です。

そんなときは、**Power Query**で置換リストを用意して、一括で文字列を変換する方法が向いています。

たとえば「㈱」「株式会社」「(株)」を同じ表記に統一したり、部署名の旧名称を新名称にそろえたりする処理は、関数よりPower Queryのほうが再利用しやすくなります。

分割と応用テクニックで実務データを効率よく加工する

文字列を分割する

文字列の分割は、CSVデータ、商品コード、メールアドレス、住所、氏名などを扱うときに必要になります。

Excelの画面操作では**「区切り位置」機能**を使って、カンマ、タブ、スペース、ハイフンなどを基準に列へ分けられます。

関数で分割する場合、Microsoft 365なら**TEXTSPLIT関数**が便利です。

たとえば、A2セルに「東京,大阪,福岡」と入っている場合、

“`
=TEXTSPLIT(A2,”,”)
“`

と入力すると、カンマを基準に横方向へ分割できます。

従来のExcelでは、LEFT、MID、FINDなどを組み合わせる必要がありましたが、TEXTSPLIT関数なら式を大幅に短くできます。

分割でよくある失敗は、**区切り文字がデータ内に複数回出てくる場合**です。

たとえば「A-001-Tokyo-2026」のようなコードから、2つ目と3つ目のハイフンの間だけを取り出したい場合、単純に最初のハイフンで分割するだけでは目的の値が得られません。

そんなときは、TEXTAFTER関数やTEXTBEFORE関数で何番目の区切り文字を基準にするかを指定するか、TEXTSPLIT関数で一度すべて分割して必要な列を参照する方法が考えられます。

より複雑な分割や大量データの処理では、**Power Query**の「区切り記号による分割」や「区切り記号の間のテキスト抽出」を使うと、手順として管理しやすくなります。

処理の順番を意識する

実務で文字列操作を安定させるには、関数名だけでなく**「処理の順番」**を意識することが大切です。

いきなり抽出や分割を始めるのではなく、まず不要な空白や表記ゆれを整えてから、抽出・結合・置換を行うとエラーが少なくなります。

たとえば、スペース区切りで氏名を姓と名に分ける場合、全角スペースと半角スペースが混在していると正しく分割できません。

先にSUBSTITUTE関数で全角スペースを半角スペースに統一し、TRIM関数で余分な空白を整えてから分割すると、結果が安定します。

よく使う実務パターン

文字列操作でよく使う実務パターンは、次のように整理できます。

– 商品コードの先頭・末尾から分類コードや年度を取り出す
– メールアドレスの@前後からユーザー名やドメインを抽出する
– 氏名、住所、部署名などを結合して表示用の文字列を作る
– 表記ゆれや旧名称を置換して集計しやすい形にそろえる
– カンマ区切りやハイフン区切りのデータを列に分割する

これらの処理は、関数だけでも十分に対応できますが、同じ加工を繰り返すならPower Queryも候補に入れると作業効率が上がります。

– **関数**：セル上で結果をすぐ確認できる。

少量のデータや一時的な加工に向いている
– **Power Query**：取り込みから整形までを手順化できる。

毎月の売上データ、外部CSV、システム出力ファイルの加工に向いている

どちらが優れているというより、**単発の処理なら関数、繰り返し作業ならPower Query**という基準で選ぶと実務に合います。

まとめ：完成形から逆算して考えよう

Excel文字列操作関数を使いこなすための近道は、**「完成形から逆算する」**ことです。

– 取り出したい文字はどこにあるのか?
– 区切り文字は何か?
– 文字数は固定か可変か?
– 置換対象は文字列か位置か?

これらを確認すれば、使う関数は自然に絞られます。

– 左・右・途中から取り出す → LEFT、RIGHT、MID
– 位置を調べる → FIND、SEARCH
– つなげる → TEXTJOIN
– 置き換える → SUBSTITUTE、REPLACE
– 分ける → TEXTSPLIT、Power Query

というように、目的別に覚えると迷いにくくなります。

文字列操作を身につけると、データ整形にかかる時間を減らして、集計や分析に使えるきれいな表を短時間で作れるようになります。

最初は難しく感じるかもしれませんが、よく使うパターンから少しずつ覚えていけば大丈夫です。

ぜひ実務で試してみてください!

広告