特定の形式の文字列を削除する

使う関数
カテゴリー 文字列操作
使えるバージョン
  • 古いExcelでも可
作業に使うデータ produce101japan-members-1

Question

C列の「生年月日」は現状、(2001-08-04) 2001年8月4日（22歳） みたいな形式になっている。

sheet1

別なシート（sheet2）を作成し、そのC列に、

  • (xxxx-xx-xx) の部分を消して表示する
  • - とだけ入力されている番組辞退者のセルには、非公開 と入力する
sheet2
デモの操作方法
  • C2セルをダブルクリックして関数を確認
  • C2セルをクリックし、右下の四角を下にドラッグして関数をコピー

Answer

sheet2のセルC2に以下の関数を入力し、ドラッグ操作でセルC102までコピーする。

解答例 
=IF(ISERROR(SEARCH("(????-??-??)", C2)), "非公開", TRIM(MID(C2, 13, LEN(C2))))

Excel 2021以降やMicrosoft 365では、関数内でC2:C102のように複数のセルを一度に指定して、 Enter を押すことで、C102セルまで一気に関数が実行される。（スピル機能）

解答例（スピル機能が使える場合） 
=IF(ISERROR(SEARCH("(????-??-??)", C2:C102)), "非公開", TRIM(MID(C2:C102, 13, LEN(C2:C102))))
「C2」ではなく「C2:C102」のようにセル範囲を指定

1. IF関数の使い方を理解する

関数で、生年月日が正しく入力されている場合と、ハイフンのみ入力されている場合とで、分けて処理を行う。

IF(trueかfalseを返す条件式, trueの場合の表示, falseの場合の表示)

結果がTRUEかFALSEになるものを条件式と呼ぶ。

「〜かどうか？」という問いに対して、FALSEは「そうじゃない」、TRUEは「そうです」という意味を持つ。

=C2:C102="-"
=C2:C102<>"-"

2. 分岐のための条件を作る

(2001-08-04)のような形式の文字列を探すために、 関数か 関数を使うことを検討する。

FIND(検索文字列, 検索対象)
SEARCH(検索文字列, 検索対象)

関数は 関数の強化版。

  • 関数では具体的な文字でしか検索できない
  • 関数では、ワイルドカード（何らかの文字が入ることを表す記号）を使った検索（あいまい検索）もできる

?は何かしらの1文字を表すワイルドカードで、(2001-08-04)のような形式の文字列を探す場合は次のように書ける。

SEARCH("(????-??-??)", C2:C102)

関数の結果は、

  • 指定した文字列が見つかれば、その開始位置（何文字目か）
  • 指定した文字列が見つからなければ、#VALUEエラー

ここでは、「(2001-08-04)のような形式の文字列が含まれていない」という条件を作りたい。

は、指定したセルがエラーかどうかを調べる関数。

ISERROR(対象セル)

先ほどの 関数の結果を 関数に渡すことで、

ISERROR(SEARCH("(????-??-??)", C2:C102))
  • 関数の結果がエラーになれば、true
  • 関数の結果がエラーじゃなければ、false

という条件式ができあがる。

3. IF関数を組み立てる

先ほど作った条件式を使って、 関数を大まかに組み立てる。

IF(ISERROR(SEARCH("(????-??-??)", C2:C102)), "非公開", "TODO")

(????-??-??)という形式の文字列が存在する場合は、(????-??-??)部分を削除したものを表示したいが、今はとりあえずTODOという文字を表示するようにしている。

ダブルクォート（””）で囲んだ内容は、文字列としてそのまま表示される。

4. (xxxx-xx-xx)の部分を削除する処理を考える

TODO部分では、(xxxx-xx-xx)の部分を削除する処理を行う。

(xxxx-xx-xx)の部分を削除するということは、(xxxx-xx-xx)の最後の閉じカッコ) 以降だけを 残す（取り出す） ということ。

文字列の途中から最後まで取り出す場合は、 関数が便利。

関数は、指定した開始位置から、指定した文字数分、文字列を取り出す関数。

MID(加工前の文字列, 開始位置, 文字数)

文字列の最後まで取り出す場合は、最後の文字まで確実に取り出せる文字数を指定する必要がある。

どんなに長い文字列でも大丈夫なように、文字列全体の文字数（ 関数の結果）を指定すると安全。

MID(加工前の文字列, 開始位置, LEN(加工前の文字列))

(xxxx-xx-xx)は12文字なので、(xxxx-xx-xx)の次の文字は13文字目になる。

文字列の13文字目から最後まで取り出せばよい。

MID(C2:C102, 13, LEN(C2:C102))

5. 半角スペースを削除する

(2001-08-04) 2001年8月4日（22歳） のように、(2001-08-04) と2001年8月4日（22歳） の間には、半角スペースが空いている。

そのため、先ほどの 関数で取り出した日付と年齢部分には、先頭に半角スペースが残っている。

MID関数の開始位置13を14に変えることでも、このスペースを除いた上で日付を抽出することはできるが、万が一スペースが空いていない行もある可能性に備えて、 関数で前後のスペースを削除する方法もある。

TRIM(文字列)

関数で取り出した結果を、 関数で包むことで、スペースを消すことができる。

TRIM(MID(C2:C102, 13, LEN(C2:C102)))

6. 完成🎉

関数で抽出し、 関数でスペースを削除した結果を、先ほどのTODO部分に入れると、目的としていた処理を行う関数が完成する。

IF(ISERROR(SEARCH("(????-??-??)", C2:C102)), "非公開", TRIM(MID(C2:C102, 13, LEN(C2:C102))))