けど、そんな中で悩むことは、
・LEFTやMIDの使い方がいまいちピンとこない
・区切り文字が2つ以上あると、どこから探せばいいかわからない
ですよね。
今回はそんなお悩みを解決する
についてまとめます！
完成イメージ
「[」から「]」の間だけを抜き出すには、開始の区切りと終わりの区切りの「位置」を調べて、その間の文字数だけ切り出すのがポイントです。
今回は
・=MID(A2,SEARCH(“[“,A2)+1,SEARCH(“]”,A2)-SEARCH(“[“,A2)-1) で「[」と「]」の間を抜き出す方法
をゴールにします。
型番コードや住所から、かっこで囲まれた部分だけをまとめて取り出せるようになります。
なお、同じ記号が2つ以上あるときの探し方や、うまくいかないときの対処法（トラブルシュート）、VBAで一括抽出する方法もあわせてご紹介します。
上の画像が完成形です。「[」と「]」の間だけが、型番・住所・2つ目のかっことしてきれいに取り出されています。
もくじ
STEP1. 元データを用意する
まずは抽出したい文字列の一覧を用意します。
A列が「AX-[100]-RED」のような品番コード、B列が「東京都[新宿区]西新宿」のような住所です。
C列の「型番」とD列の「区」、F列の「2つ目」は、これから数式で埋めていきます。
E列には、かっこが2組ある住所を入れておきました。
データは10件です。
かっこの中だけを抜き出していきましょう。
下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。
| 品番コード | 住所 | 型番 | 区 | 備考 | 2つ目 |
|---|---|---|---|---|---|
| AX-[100]-RED | 東京都[新宿区]西新宿 | 東京都[新宿区]西新宿[1丁目] | |||
| BX-[250]-BLUE | 大阪府[北区]梅田 | 大阪府[北区]梅田[2丁目] | |||
| CX-[080]-GREEN | 愛知県[中区]栄 | 愛知県[中区]栄[3丁目] | |||
| DX-[420]-BLACK | 福岡県[博多区]博多駅前 | 福岡県[博多区]博多駅前[1丁目] | |||
| AX-[150]-WHITE | 東京都[渋谷区]道玄坂 | 東京都[渋谷区]道玄坂[2丁目] | |||
| BX-[300]-YELLOW | 北海道[中央区]大通 | 北海道[中央区]大通[4丁目] | |||
| CX-[095]-GRAY | 兵庫県[中央区]三宮 | 兵庫県[中央区]三宮[1丁目] | |||
| DX-[500]-PINK | 京都府[中京区]河原町 | 京都府[中京区]河原町[5丁目] | |||
| AX-[210]-ORANGE | 神奈川県[中区]山下町 | 神奈川県[中区]山下町[2丁目] | |||
| BX-[175]-PURPLE | 埼玉県[大宮区]桜木町 | 埼玉県[大宮区]桜木町[3丁目] |
※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。
STEP2. 「[」から「]」の間を抜き出す（MID＋SEARCH）
まずは品番コードの「[」と「]」の間、つまり「100」の部分を抜き出します。
C2セルに次の数式を入力します。
=MID(A2,SEARCH("[",A2)+1,SEARCH("]",A2)-SEARCH("[",A2)-1)
MID関数は「開始位置」から「文字数」の分だけ文字を取り出す関数です。
開始位置は「[」の1つ後ろなので、SEARCHで調べた位置に1を足します。
文字数は「]の位置 − [の位置 − 1」で、かっこの間の文字数になります。
A2が「AX-[100]-RED」なら「[」は4文字目、「]」は8文字目です。
そのため4+1=5文字目から、8−4−1=3文字分を取り出して「100」が返ります。
実際の入力画面がこちらです。
STEP3. 数式を下にコピーして使い回す
C2セルに入れた数式を、C11セルまで下にコピーします。
数式の中の A2 は相対参照なので、コピー先では自動で A3、A4…と1行ずつずれていきます。
そのため、1つ数式を作るだけで10件すべての型番が一度に取り出せました。
1件ずつ手で切り出す必要はありません。
コピーするだけでまとめて処理できるのでカンタンですね。
「A」から「B」の間を抜き出す基本形は =MID(元, SEARCH(“[“,元)+1, SEARCH(“]”,元)-SEARCH(“[“,元)-1) です。
開始位置＝[の位置＋1、文字数＝]の位置−[の位置−1 と覚えましょう。
この数式はSEARCHを3回書いていますが、1行分をコピーすれば各行で自動計算されるので、気にせず使い回して大丈夫です。
STEP4. 住所も同じ数式で抜き出す
考え方は同じなので、住所の「[」と「]」の間も同じ形で取り出せます。
D2セルに次の数式を入力し、D11セルまでコピーします。
=MID(B2,SEARCH("[",B2)+1,SEARCH("]",B2)-SEARCH("[",B2)-1)
参照するセルがB2に変わっただけで、区切り文字は同じ「[」と「]」です。
「東京都[新宿区]西新宿」からは「新宿区」だけが取り出せました。
区切り文字を変えたいときは、SEARCHの中の「[」「]」を書き換えるだけです。
STEP5. 同じ記号が2つあるときは「2つ目」を探す
E列の「東京都[新宿区]西新宿[1丁目]」のように、かっこが2組ある文字列ではひと工夫必要です。
SEARCH関数は、指定しない限り最初に見つかった位置しか返しません。
そのため、さきほどの数式のままだと最初の「[新宿区]」しか取り出せません。
2つ目のかっこを探すには、SEARCHの3つ目の引数に開始位置を指定します。
F2セルに次の数式を入力します。
=MID(E2,SEARCH("[",E2,SEARCH("[",E2)+1)+1,SEARCH("]",E2,SEARCH("]",E2)+1)-SEARCH("[",E2,SEARCH("[",E2)+1)-1)
内側の SEARCH(“[“,E2)+1 で「1つ目の[の次の位置」を求め、そこから探し直しています。
こうすると2つ目の「[」と「]」の位置がわかり、その間の「1丁目」だけを取り出せます。
数式は長く見えますが、やっていることはSTEP2と同じ「間を抜き出す」だけです。
実際の入力画面がこちらです。
F列まで、すべてのかっこの中身が取り出せました。
区切り文字が何組あっても、「どこから探すか」をずらせば同じ考え方で対応できます。
抽出方法の比較
やりたいこと別に、使う数式を表にまとめました。
「かっこが1組か2組か」で使い分けると迷いません。
| やりたいこと | 数式 | ポイント |
|---|---|---|
| 「[」と「]」の間を取り出す | =MID(A2,SEARCH(“[“,A2)+1,SEARCH(“]”,A2)-SEARCH(“[“,A2)-1) | 開始＝[の位置＋1、文字数＝]−[−1 |
| 2つ目のかっこの間を取り出す | SEARCH(“[“,E2,SEARCH(“[“,E2)+1) を開始位置に使う | SEARCHの3つ目に開始位置を指定する |
| 区切りが無いときエラーを出さない | =IFERROR(MID(A2,…),””) | #VALUE! を空欄にする |
| VBAで一括抽出する | InStr＋Mid／Split | 件数が多いときや定型作業に向く |
VBAで「[」と「]」の間を一括抽出する
件数が多く、毎回同じ抽出をするなら、VBAで一括処理するのがもっともラクです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。
Sub ExtractBetween()
Dim r As Long, s As String, tmp As String
For r = 2 To 11
' [ と ] の間を抜き出す（InStrで位置を調べる）
s = Cells(r, 1).Value
Cells(r, 3).Value = Mid(s, InStr(s, "[") + 1, InStr(s, "]") - InStr(s, "[") - 1)
' 住所も同じ考え方
s = Cells(r, 2).Value
Cells(r, 4).Value = Mid(s, InStr(s, "[") + 1, InStr(s, "]") - InStr(s, "[") - 1)
' 2つ目のかっこは Split で分ける
tmp = Split(Cells(r, 5).Value, "]")(1)
Cells(r, 6).Value = Split(tmp, "[")(1)
Next r
End Sub実行すると、C列・D列・F列が一瞬で埋まります。
VBAの InStr関数 はSEARCH関数と、Mid関数 はMID関数とそれぞれ同じ役割です。
2つ目のかっこは、Split関数で「]」と「[」の位置を分けて取り出しています。
「東京都[新宿区]西新宿[1丁目]」を「]」で分けると「西新宿[1丁目」が2番目なので、そこから「[」で分けて「1丁目」を取り出しています。
数式でSEARCHの開始位置をずらすのと、やっていることは同じなのでカンタンですね。
うまくいかないときの対処法（トラブルシュート）
実際に試すと、思ったようにいかないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。
| 症状 | 原因 | 対処 |
|---|---|---|
| #VALUE! になる | その行に区切り文字「[」「]」が無い | =IFERROR(MID(A2,SEARCH(“[“,A2)+1,SEARCH(“]”,A2)-SEARCH(“[“,A2)-1),””) でエラーを空欄にする |
| 同じ記号があるのに2つ目が取れない | SEARCHは最初に見つかった位置しか返さない | SEARCH(“[“,E2,SEARCH(“[“,E2)+1) と開始位置を指定して2つ目を探す |
| 全角「［］」の行だけ抽出できない | 「［」と「[」は見た目が似ていても別の文字 | =SUBSTITUTE(A2,”［”,”[“) で半角に統一してから検索する |
| 「080」が「80」になってしまう | MIDの結果を数値として扱うと、先頭の0が消える | =TEXT(MID(A2,SEARCH(“[“,A2)+1,SEARCH(“]”,A2)-SEARCH(“[“,A2)-1),”000”) で桁を揃える／セルを文字列書式にする |
関数の説明
今回使うのは、文字の位置を調べるSEARCH関数と、途中から切り出すMID関数です。
よく似たFIND関数もセットで押さえておきましょう。
MID関数（ミッド）
| 書式 | =MID(文字列, 開始位置, 文字数) |
|---|---|
| できること | 文字列の途中から、指定した文字数だけ取り出します。 |
| 戻り値 | 取り出した文字列 |
| こんなときに | 「開始の区切りより後ろ」から「終わりの区切りまで」だけを取り出したいとき。文字数を「終了位置−開始位置−1」にすると間だけが取れます。 |
| 注意 | 開始位置が文字列の長さを超えると、空文字（””）になります。 |
=MID(A2,SEARCH("[",A2)+1,SEARCH("]",A2)-SEARCH("[",A2)-1)
SEARCH関数（サーチ）
| 書式 | =SEARCH(検索文字列, 対象文字列, [開始位置]) |
|---|---|
| できること | 指定した文字が何文字目にあるかを返します。 |
| 戻り値 | 位置（数値） |
| こんなときに | 「[」や「]」など、区切り文字の位置を知りたいとき。 |
| 注意 | 大文字と小文字を区別しません。3つ目の開始位置を指定すると、2つ目以降の位置も探せます。 |
FIND関数（ファインド）
| 書式 | =FIND(検索文字列, 対象文字列, [開始位置]) |
|---|---|
| できること | SEARCH関数とほぼ同じで、指定した文字の位置を返します。 |
| 戻り値 | 位置（数値） |
| SEARCHとの違い | FINDは大文字と小文字を区別します。SEARCHは区別しません。区切り文字が英字で、大文字小文字を分けたいときにFINDを使います。 |
| 注意 | FINDはワイルドカード（* ?）を使えません。あいまい検索をしたいときはSEARCHを使います。 |
あわせて使う応用の関数
| IFERROR関数 | =IFERROR(数式, エラーのときの値) 区切り文字が無いときの #VALUE! を空欄などに置き換えます。 |
|---|---|
| SUBSTITUTE関数 | =SUBSTITUTE(文字列, 検索文字列, 置換文字列) 全角「［］」を半角「[]」に統一するときに使います。 |
| VALUE関数 | =VALUE(文字列) 抜き出した文字列を数値に変換します。数値にすると先頭の0は消えるので注意しましょう。 |
完成
「[」と「]」の間だけを、型番・住所・2つ目のかっこと、きれいに取り出せました。
思ったとおりに動きましたか？
どの列も数式なので、元の文字列を書き換えれば結果も自動で変わります。
手作業での切り貼りから卒業すると、データ整理がぐっとラクになります。
さいごに
いかがでしょうか。
今回は、
についてまとめました。
ポイントは、開始と終わりの区切りの位置をSEARCH関数で調べ、
文字数を「終了位置−開始位置−1」にしてMID関数で切り出すことです。
同じ記号が2つ以上あるときは、SEARCHの開始位置をずらせば対応できます。
まずは今回のサンプル表で、ぜひ一度試してみてください。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。
この記事の関連キーワード
「文字列」の関連記事
こちらの記事の内容「文字列」に関連する記事一覧です。
コメントを残す