Excel中級者

[保存版]Excelで指定の文字列を抽出する方法7選

しんたろ。
この記事でわかること
  • 文字列の分割・結合の知識が身につく
  • 住所リストの中で「都道府県」を細分化する。
  • 違うセル同士を簡単に結合する。
  • 電話番号で”市外局番だけ”指定して削除する
  • 市内局番を”( )”で括る ・・・etc

 この記事を見ると「文字列の分割・結合を関数を使いこなして意のまま操る知識」が身につきます。

 データを取り扱う上で、キレイなデータだと「フラッシュフィル」機能で簡単に抽出できます。

ですが、データに一貫性が無く乱れているデータだとデータベースとしては不十分なケースも多いですよね。こういう手作業になってしまうところを是非関数を利用してデータを整えましょう。

①ある条件を満たす文字列を抽出する:IF関数・MID関数・LEFT関数

 例として、都道府県のデータを参考に説明をします。

都道府県ごとの集計や抽出を簡単に行いたい時は、都道府県と市区町村以下に住所を分割する必要があります。まずはセルに入力されている住所から都道府県を取り出しましょう!

使用する関数はIF関数LEFT関数・MID関数です。

>>IF関数の説明はこちら

>>LEFT関数の説明は近日公開予定です。

>>MID関数の説明は近日公開予定です。

 この3つの関数を使って以下のような意味の関数式を組み立て、N列にある住所から都道府県のみを取り出していきましょう。式は言葉で論理的に組み立てていくと理解しやすくなりますよ。

=IF(MID(A2,4,1)=”県”,LEFT(A2,4),LEFT(A2,3))

「もし、住所のセルの左から4番目が「県」ならば、左から4文字、それ以外は左から3文字取り出します。」

関数説明文字列検索

②セルの途中から文字列を抽出する方法:MID関数・LEN関数

 住所が入力されているセルから市区町村以下を取り出してみましょう。

使う関数はLEN関数MID関数です。流れとして

  • LEN関数で都道府県の文字数を特定
  • MID関数に入れ込み必要な箇所だけ抽出

>>LEN関数の説明は近日公開予定です。

=MID(A2,LEN(C2)+1,LEN(A2)-LEN(C2))

セルA2の「北海道札幌市中央区大通西」を選択。開始位置はC2の「北海道3文字+1」。文字数はセルA2「北海道札幌市中央区大通西9文字」- セルC2「北海道3文字」」

関数説明MID関数LEN関数

文字数をどう操るかががポイントだね♪

③別のセルと文字列を結合する方法:CONCATENATE関数

 次はB列に入っている番地と取り出したD列を結合して1つのセルにまろめる方法になります。

使用する関数はCONCATENATE関数です。

 使い方はとてもシンプルで、関数の引数に対象セルを範囲指定すればその中のセルが結合されます。今回は離れた2つのセルを結合します。

=CONCATENATE(D2,B2)

D2の「札幌市中央区大通西」とB2の「1丁目」を結合」

関数説明CONCATENATE関数

お手軽に「&」で表示する方法

2つのセルを結合する方法として「&」で結合する方法もあります。

&だけで結合できるじゃん!と思うかもしれませんが、セルの結合において3つ以上になってくるとめんどくさくなるため、その場合は関数を使った方が良いです。

&で結合

先ほどのCONCATENATE関数を使った時と同じように抽出することができます。

結合の方法については、以下の記事で詳しく解説していますで併せてご確認ください。

>>結合方法の詳しい解説をみる

住所などの場合は全角で統一すること

カタカナ・数字を全角で統一したい場合はJIS関数を使うと良いです。

関数説明JIS関数

④検索して文字を抽出する:FIND関数・LEFT関数

メールアドレスを例えに説明をします。

「@の前のアルファベットを取り出したい」

この悩みを簡単に取り出す方法を伝授します。

使う関数はFIND関数・LEFT関数

>>FIND関数の説明は近日公開予定です。

FIND関数は指定した文字列を検索し、ヒットした文字列の先頭が左から何文字目にあるかを抽出してくれます。実際に見てみましょう。

=LEFT(B2,FIND(“@”,B2)-1)

FIND関数でB2のメールアドレスにある”@”までの文字数を検索。”@”は含めないので-1。LEFT関数で対象セルを引数1にし、引数2に先ほどのFIND関数を入力するとOK」

関数説明FIND関数

⑤大文字・小文字に統一する、頭文字のみ大文字に変換する:UPPER関数・LOWER関数・PROPER関数

文字列を操作する際に便利な関数を覚えておきましょう。

使用する関数はUPPER関数・LOWER関数・PROPER関数です。

  • UPPER関数は全部大文字に
  • LOWER関数は全部小文字に
  • PROPER関数は頭文字だけ大文字に

とてもシンプルで使いやすい関数なので覚えておきましょう。

UPPER関数は全部大文字に  LOWER関数は全部小文字に  PROPER関数は頭文字だけ大文字に

⑥文字数を指定して削除する:REPLACE関数

 電話番号をつかって説明します。

使用する関数はREPLACE関数です。

REPLACE関数は文字を置換し、置換後の文字を空白に指定すると削除が可能になります。

 この関数は「左から何文字目を開始位置とし、何文字分をまとめて何に置換するか」となります。

例えば電話番号の局番をすべて消したい時に、「090-」「080-」の二つが混在しているため、文字で指定すると何度も置換をしないといけないハメになります。

REPLACE関数なら一度に置換可能です。

関数説明REPLACE関数

⑦文字をまとめて置換する:SUBSTITUTE関数

 「文字数指定ではなくて、文字の検索と置換をしたい。」

使う関数はSUBSTITUTE関数です。

 REPLACE関数は文字数指定で置換していましたが、SUBSTITUTE関数は文字を指定して置換できます。

電話番号のように「-」が2ヶ所に含まれている場合、最初の「-」は「(」に、2つ目の「-」は「)」に置換することができます。

実際に見ていきましょう。

=SUBSTITUTE(SUBSTITUTE(B2,”-“,”(“,1),”-“,”)”,1)

SUBSTITUTE関数にSUBSTITUTE関数をネスト。SUBSTITUTEで始めの「-」を「(」に変えたのち、2つ目の「-」を「)」に変換」

関数説明SUBSTITUTE関数

まとめ

おさらい:文字列の分割・結合の知識
  • 住所リストの中で「都道府県」を細分化する。
  • 違うセル同士を簡単に結合する。
  • 電話番号で”市外局番だけ”指定して削除する
  • 市内局番を”( )”で括る ・・・etc

 文字列を操作できるようになると、データの精査を効率よく行うことができます。

「面倒」「しんどい」「楽したい」

関数を組み合わせると、今までの苦労が無くなるくらい意外とできてしまうものが多いです。

関数を理解して組み合わせることでオリジナルの関数を見つけてみてください♪

 

全会社員を残業地獄から救いたい
本ブログの有効活用方法
1
Concept
初心者に分かりやすい解説

しんたろ。ブログでは「少しでも残業を減らしたい」「時間効率を上げて新しいことにチャレンジしたい」「仕事で評価を上げて年収を上げたい」そんなあなたに向けて私が学んできたノウハウを最短ルートで教えるExcelブログとなっています。

覚えておくべき関数や操作方法を徹底解説!

2
Concept
Excelを最短で
習得する方法を解説

「独学で習得できる気がしない」そんな方に効率よく習得できる記事を書きました。

本や動画を紹介していますが、やはり一番効率の良い習得方法は、講師にわからないことを質問できる『オンラインスクール』です。

3
Concept
Excelが大活躍!
会社員でも不就労所得を作れる。

「会社の収入だけでは不安」そう思い出会った副業がブログでした。書いた記事たちが、あなたが寝ている間もずっと働いてくれます。『不就労』の完成です。

記事を書く際の様々な分析にExcelがとても役立ちます。私は2023年末までにブログで脱サラする予定です!

あなたもブログで脱サラしましょう。

お問い合わせ

Contact

問い合わせより直接相談していたくと
無料で相談にのりますよ♪

お気軽にご相談ください♪


ABOUT ME
しんたろ。
しんたろ。
脱サラ目指す会社員
デスクワークに追われ、仕事が極端に遅かった私がExcelを学んだら会社から評価され残業も大幅削減を実現!早期FIREするための副業もしています。 あなたの人生も好転させたい!ぜひ本ブログをご活用ください。
記事URLをコピーしました