こんにちは、ゆんつです。
先日、仕事でエクセルに作業日報の備考欄だけを集めた一覧表を作り、それを日付順にソートするという作業をしなければならなくなりました。
具体的に言うと、以下のような感じでセルに必ず西暦による日付（日付は全て2000年以降）が1つ含まれた文字列があるので、この文字列の中から日付だけを抽出して、備考欄を日付順にソートするという作業です（表の内容は全くの架空で実際のものとは違います）
この画像では備考欄の文字列は短く行数も少ないですが、実際の作業に使った表はもっと備考欄の文字列が長くて行数は数千行。
日付の位置も文字列の最初にあったり、途中にあったり、最後にあったりバラバラ。
こんな表に含まれる日付を手作業で抽出したら、どれくらい時間がかかるかわかりません。
これまでに1回もやったことがない作業なので、関数を調べながら悪戦苦闘することしばし。
なんとか文字列の中から日付だけを抽出することができました。
ふぅ
今日は
について、備忘録として書き残しておきたいと思います。
文字列の中から日付だけを抽出する
それでは、エクセルの関数を使って文字列の中から日付を抽出したいと思います。
使う関数
使用した関数は以下の通り。
| 関数名 | 役割 | 構文 |
| MID | 文字列の指定された位置から指定された文字数の文字を返す | MID(文字列, 開始位置, 文字数) |
| FIND | 指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返す | FIND(検索文字列, 対象, [開始位置]) |
| SEARCH | 指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返す | SEARCH(検索文字列,対象,[開始位置]) |
FINDとSEARCHは同じように見えますが、FINDは検索文字列にワイルドカードが使えませんが、SEARCHはワイルドカードが使えるなどの違いがあります。
考え方
文字列から日付を抽出する場合、
- 文字列の中で日付が何文字目に登場するか
- 日付の文字数
を調べることができれば抽出が可能となります。
文字列の中で日付が何文字目に登場するか調べる
例えば、以下のような文字列の場合。
日付が登場する位置は、左から文字を数えて10番目です。
この、日付が何文字目に登場するかを調べるのにSEARCH関数を使用します。
SEARCH関数を使用する理由はワイルドカードが使えるから。
任意の1文字に該当するワイルドカードは?なので、検索文字列を"20??年"にすることで2000年から2099年までの文字列と一致するようになります。
FIND関数の場合はワイルドカードが使えず、年数と一致させようとして検索文字列を"20"にすると、個数や台数の20にも一致してしまうので、ワイルドカードが使えるSEARCH関数を使用して年だけに一致するようにします。
この部分を算式にすると
となります。
これで、各セルの文字列に含まれる日付の登場位置がわかりました。
日付につかわれている文字数を調べる
続いては、日付につかわれている文字数を調べます。
これはワイルドカードを使う必要が無いのでFIND関数を使って、SEARCH関数で調べた日付が登場する位置以降で最初に"日"が出てくる位置を調べます。
これを算式にすると
となります。
第3引数にSEARCH("20??年",検索対象となるセル)を設定することで、日付が登場する位置以降で初めて出てくる「日」が何文字目かを調べることができ、日付以外で使われている「日」に一致しないようにしています。
これで、日付の表記に使われている「日」が何文字目に登場するかがわかりました。
そして、日付に使われる文字数は
"日"が登場する位置 - 日付が登場する位置 + 1
で求めることができます。
これを算式にすると
となります。
これで、抽出すべき日付の文字数がわかりました。
日付を抽出する
日付の開始位置と日付の文字数がわかったら、あとはMID関数で抽出するだけです。
算式にすると
となります。
これで、日付の抽出は完了です！
1900年代が含まれるとき
というわけで、無事エクセルで文字列の中から日付を抽出することができました。
もう一度算式を書いておくと、以下の通りです。
今回は文字列に含まれていた日付が2000年以降だったので、日付の開始位置の取得に"20??年"という感じのワイルドカードを使用しましたが、もし1900年代の日付も含まれている場合は年数の部分は全てワイルドカードで"????年"とすると良いかもしれません。
もし、今回の方法以外にもっと良い方法があったら、ご教授いただけると嬉しいです。
教えてね
それでは、またー。
コメント