Excel大好きプログラミングOLこと、くらげ(@owasa7)です😃
今回の記事では、LEFT関数・FIND関数を組み合わせてセル内の余計な文字を取り除く方法を解説していきます。
せっかく綺麗に書式がまとまってたのに、だれかが余計な文字をいれてぶち壊した・・・。
お客様からもらった販売実績のファイル、製品名の横に独自の識別コードが入ってる😨😨
特殊な例に聞こえるかもしれませんが、エクセルって意外と社内の独特な文化が反映されがちじゃないですか？(笑)
そんな厄介なやつらを取り除く方法を、実例を使って紹介します。
この記事で紹介する関数：
- LEFT: 左から指定された文字数分、セルの値を抽出する。
- FIND: 指定された文字が、左から何番目にあるかを返す。
- IF: 条件により処理を分岐させ、真なら真の値を、偽なら偽の値を返す。
- COUNTIF: 指定された条件に合致するセルの個数を返す。
LEFT関数・FIND関数を組み合わせて、余計な文字を取り除く方法
たとえば、下記のような表があったとします。
商品名はくだものの名前だけだったのに、だれかがハイフンをいれてくだものの種類を追記してしまいました💦💦
元通りにするために、LEFT関数とFIND関数を組み合わせて必要な文字列だけ抽出してしまいましょう！
まずは、LEFT関数・FIND関数のそれぞれの働きについてを紹介します。
LEFT関数の使い方
LEFTはその名前の通り、左から指定した数分の文字を返してくれます。
| 数式 | +LEFT(C4, 3) |
| 意味 | 対象セルの左から3番目までを抽出 |
この場合、ハイフン以前の「りんご」のみを抽出したいので、文字数は「3」を指定しました。
すると、くだものの名前はそれぞれ文字数が異なるため、途切れたり「ー」が入った名前が返されてしまいます・・。
このままだと上手くいきません。
FIND関数の使い方
FINDは、検索した文字が左から何番目にあるのかを返します。
| 数式 | +FIND(“-“*, C4) |
| 意味 | 場所を知りたい文字を入力、対象セルを指定、開始位置**は指定しないと1として認識 |
ハイフンを指定したので、それが左から何番目にあるのかを返してくれます。
キウイ-イエローなら、4。パッションフルーツ-フィリピン産なら10となります。
*文字列を指定する場合は、””で囲みましょう。これで文字であることを認識します。
**検索文字が2個以上ある場合に使用。何文字目以降を指定できる。(例)cookieの2個目の「o」を調べたい場合は、3文字目以降にあるので「3」とする。
LEFT関数とFIND関数を組み合わせると・・。
それでは、この二つを組み合わせてみましょう。
| 数式 | +LEFT(C4, FIND(“-“,C4)) |
| 意味 | 対象セルを指定、検索文字を入力、対象セル(LEFTの対象セルと同じ)を指定 |
LEFTで抽出したいセルを選び、FINDで何番目の文字数まで抜き出すかを設定します。
でもこのままだと処理結果がおかしいことに・・・。
それは、FINDでハイフンまでの文字数を検索しているから。
そこでFINDの数式のあとに-1を入れてあげましょう。これでハイフンより1文字前までを抽出してくれます。
| 数式 | +LEFT(C4, FIND(“-“,C4)-1) |
| 意味 | FINDの結果からマイナス1をして1文字前を抽出 |
キウイ-イエローなら(4-1)なので3。パッションフルーツ-フィリピン産なら(10-1)なので9となります。
IF関数・COUNTIF関数を組み合わせて、さらに上級の使い方に
LEFT関数とFIND関数を組み合わせてハイフン以前の文字を抽出する方法をご紹介しました。
しかし下記表のように、ハイフンが含まれるセルと含まれないセルが混在する場合、
どうなるでしょうか？
検索文字が含まれないセルでは値が返されず、#VALUE!とエラーが発生してしまいました。
LEFT・FINDをこのまま使うには、すべての対象セルに検索文字が含まれている必要があるようです💦💦
そうはいっても、一つの関数ですべての計算を終わらせたいですよね。
その場合は、さらにIF関数・COUNTIF関数を追加します！盛り上がってきましたね！！
*COUNTIFの詳しい説明は下記の記事で紹介しております。ご参考ください。
| 数式 | +IF(COUNTIF(C4,“*-*”),LEFT(C4,FIND(“-“,C4)-1),C4) |
| 範囲 | 対象セルを指定 |
| 検索条件 | ハイフンを指定、ハイフンを挟んでいる*はワイルドカードです* |
| 文字列 | 範囲と同じセルを指定 |
| 検索文字 | ハイフンを指定 |
| 対象 | 範囲と同じセルを指定 |
| -1 | FINDでの計算から-1することで、ハイフンより位置文字前を表す |
| 偽の場合 | ハイフンがない場合は指定のセルの値を返します |
*ワイルドカードについては、ほかの記事で紹介します。この場合はハイフンを含む場合となります。
ながーい関数が完成しました！それぞれの関数の働きは下記の通りです。
- IF: COUNTIFの結果が正だった場合、LEFT＋FINDの結果を返す。偽の場合は、そのままの商品名を返す。
- COUNTIF: ハイフンが含まれている場合は1を、ない場合は0を返す。
- LEFT: FINDで返された文字数を左から抽出する。
- FIND: 対象のセルから、ハイフンが何番目にあるか返す。
だいぶごちゃごちゃで、混乱するかもしれませんが・・・とりあえずできました❤
いろいろな関数を組み合わせすと、幅広い計算ができるということを知っていただけると嬉しいです😍😍😍
みなさんもぜひ色んな関数を組み合わせて、遊んでみてはいかがでしょうか。
