区切り文字データ抽出
やりたいこと
| ＜区切り文字データ＞ | ＜抽出＞ | |
|
00001,田中,09000000000,120000,20121101 00002,鈴木,09000000001,10150,20130110 00003,佐藤,09000000002,15000,20130215 00004,井上,09000000003,97,20130301 | ⇒ |
田中 鈴木 佐藤 井上 |
カンマ区切りデータなど、特定の文字で区切られたレコードから必要なカラムだけ抽出するといった関数がありそうでなかったので、実現させてみました。
ポイントとしては、最終的な抽出は MID関数 により範囲していにてピンポイントで抽出します。 そのためには、nカラム目のデータが何文字目～何文字という情報が必要になってきます。 そこで、登場するのが、 SUBSTITUTE関数 と FIND関数 です。
FIND関数
指定した文字列を他の文字列内から検索し、その検索文字列の位置を返します。
ということは、単純にカンマで検索すればと思う人もりかもしれませんが、ここで壁が。。。
カンマ区切りのデータをカンマで検索すると、１カラム目と２カラム目の間のカンマしか検索ができません。
そこで役に立つのが...
SUBSTITUTE関数
検索文字列で指定した文字列が複数含まれている場合は、置換対象で何番目の文字列を置き換えるか指定する事ができます
もうおわかりですね？
SUBSTITUTE関数 で何番目の区切り文字かを検索し、そのレコードに含まれない文字列に置換してしまえば、あとは FIND関数 を用いて、何文字目かを特定すればいいのです。
では、レコードに含まれない文字はケースバイケースになってくるかと思いますが、
私の場合は、"改行"に置換をしています。
実践
では具体的にどうやって対象カラムを抽出しているかここから書いていきます。
| ＜対象レコード＞ | ＜抽出＞ | |
| 00001;田中;09000000000;120000;20121101 | ⇒ |
田中 |
例えば上記のような;(セミコロン)区切りレコードがセルA1に入っているとします。
ここでは2カラム目の"田中"、を抽出しようとします。(今回は改行に一旦置換します)
①開始位置の取得
FIND(CHAR(10), SUBSTITUTE(";"&A1&";", ";", CHAR(10), 2), 1)
②終了位置の取得
FIND(CHAR(10), SUBSTITUTE(";"&A1&";", ";", CHAR(10), 2 + 1), 1)
③2カラム目抽出
MID(A1, ①, ② - ① - 1)
※SUBSTITUTEの検索される文字列(A1)の前後に区切り文字";"を付与しているのは、1カラム目検索または最終カラム目の検索にも対応できるためです。
参考シート
カンマ区切り抽出の参考ファイルを作成してみました。
⇒サンプルファイルをDL
おすすめ
書籍
一冊あるだけでだいぶちがいますよね！
特にマシン室で作業しているときは、ネット検索できないので強い味方ですね！
ソフト
Officeは仕事の定番ソフト！
