LEFT関数とFIND関数で、余計な文字を取り除こう!

leftとfindで余計な文字を取り除こう Excel

くだものの表だったのに、誰かが他の情報をハイフンで追加してしまいました・・。

元通りにするために、LEFT関数とFIND関数を使って必要な文字列だけ抽出してしまいましょう!

LEFT関数とFIND関数を組み合わせる

まずは、LEFT関数・FIND関数のそれぞれの働きについてを紹介します。

LEFT関数の仕組み

LEFT関数の使い方
★LEFT: 左から指定された文字数分、セルの値を抽出する。
+LEFT(文字列, 文字数)
数式+LEFT(C4, 3)
意味対象セル左から3番目までを抽出

ハイフン以前のりんごのみを抽出したいので、文字数は3を指定しました。

すると、くだものの名前はそれぞれ文字数が異なるため、途切れたり-が入った名前が返されてしまいます・・。

このままだと上手くいきません。

FIND関数の仕組み

FIND関数の使い方
★FIND: 指定された文字が、左から何番目にあるかを返す。
+FIND(検索文字,対象,開始位置)
数式+FIND(“-“, C4)
意味場所を知りたい文字*を入力、対象セルを指定、開始位置*は指定しないと1として認識

*文字*列を指定する場合は、””で囲みましょう。これで文字であることを認識します。

*検索文字が2個以上ある場合、4番目以降の文字から検索など開始場所の指定ができる

ハイフンを指定したので、それが左から何番目にあるのかを返してくれます。

キウイ-イエローなら、4パッションフルーツ-フィリピン産なら10となります。

LEFT関数とFIND関数を組み合わせると・・。

それでは、この二つを組み合わせてみましょう。

LEFT関数とFIND関数を組み合わせる
+LEFT(文字列, FIND(検索文字, 対象))
数式+LEFT(C4, FIND(“-“,C4))
意味対象セルを指定、検索文字を入力、対象セルを指定(LEFTの文字列と同じ)

LEFTで抽出したいセルを選び、FINDで何番目の文字数まで抜き出すかを設定します。

でもこのままだと処理結果がおかしいことに・・・。

LEFT関数とFIND関数を組み合わせる
あれ? ハイフンが残っちゃう・・
数式 +LEFT(C4, FIND(“-“,C4)-1)
意味FINDの結果からマイナス1をして1文字前を抽出

それは、FINDでハイフンまでの文字数を検索しているから。

そこでFINDの数式のあとに-1を入れてあげましょう。これでハイフンより1文字前までを抽出してくれます。

LEFT関数とFIND関数を組み合わせる
できた!

キウイ-イエローなら(4-1)なので3パッションフルーツ-フィリピン産なら(10-1)なので9となります。

IF関数・COUNTIF関数を組み合わせて、さらに上級の使い方に

LEFT関数とFIND関数を組み合わせてハイフン以前の文字を抽出する方法をご紹介しました。

しかし下記表のように、ハイフンが含まれるセル含まれないセルが混在する場合、

どうなるでしょうか?

LEFT関数とFIND関数を組み合わせる

検索文字が含まれないセルでは値が返されず、#VALUE!とエラーが発生してしまいました。

LEFT・FINDをこのまま使うには、すべての対象セルに検索文字が含まれている必要があるようです💦💦

そうはいっても、一つの関数ですべての計算を終わらせたいですよね。

その場合は、さらにIF関数COUNTIF関数を追加します!盛り上がってきましたね!!

*なお、COUNTIFの詳しい説明は下記の記事で紹介しております。ご参考ください。

IF関数・COUNTIF関数・LEFT関数・FIND関数を組み合わせる
★IF: 条件により処理を分岐させ、真なら真の値を、偽なら偽の値を返す。
★COUNTIF: 指定された条件に合致するセルの個数を返す。
+IF(COUNTIF(範囲, 検索条件), LEFT(文字列, FIND(検索文字,対象)-1), 値が偽の場合)
数式+IF(COUNTIF(C4,“*-*”),LEFT(C4,FIND(“-“,C4)-1),C4)
範囲対象セルを指定
検索条件ハイフンを指定、ハイフンを挟んでいる*はワイルドカードです*
文字列範囲と同じセルを指定
検索文字ハイフンを指定
対象範囲と同じセルを指定
-1FINDでの計算から-1することで、ハイフンより位置文字前を表す
偽の場合ハイフンがない場合は指定のセルの値を返します

*ワイルドカードについては、ほかの記事で紹介します。

ながーい関数が完成しました!それぞれの関数の働きは下記の通りです。

  • IF関数: COUNTIFの結果が正だった場合、LEFT+FIND関数の結果を返す。偽の場合は、そのままの商品名を返す。
  • COUNTIF関数:ハイフンが含まれている場合は1を、ない場合は0を返す。
  • LEFT関数: FINDで返された文字数を左から抽出する。
  • FIND関数: 対象のセルから、ハイフンが何番目にあるか返す。

だいぶごちゃごちゃで、混乱するかもしれませんが・・・とりあえずできました!

いろいろな関数を組み合わせすと、幅広い計算ができるということを

知っていただけると嬉しいです😍😍😍

みなさんもぜひ色んな関数を組み合わせて、遊んでみてください!

タイトルとURLをコピーしました