Excel。セル内の文字列分割をどうしても関数を使ってやってみたい。
TRIM&MID&SUBSTITUTE&REPT&COLUMN関数
以前紹介しました、セル内の文字列を分割したいというテクニック。
個人的には、区切り位置で十分対応が出来るので区切り位置でいいとは思うのですが、
【関数を使ってできませんか？】とご質問がありまして…。
結構考え方も作り方も大変なのですが、そんな声に応えてご紹介をしていきます。
なお、今回登場する関数は…
TRIM関数
MID関数
SUBSTITUTE関数
REPT関数
COLUMN関数
と、文字操作関係の関数総動員？です。
さて、下記の表があります。
B列にある文字列を分割していくわけです。
使用例としては、
ミドルネームがあるばあいの氏名とか、
組織や配属部署で分けたい場合などが使用するケースだと思います。
そして、ポイントなのですが、区切りたいところには、半角空白が一つ入力されています。
真田 源次郎 信繁
真田と源次郎の間に、半角空白が入っています。これが重要になります。
通常ですと、LEFT関数やRIGHT関数。
そして、MID関数を使って分割していくのが多いのですが、
今回のように、半角空白から次の半角空白までの文字数が一定でないのと、
いくつ区切られるものがあるのかが、一定でなく、
バラバラな場合を考慮して関数を作っていくことになります。
C2の数式は、
=TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",50)),COLUMN(A2)*50-49,50))
というのを作っていきます。
C2をクリックして、TRIM関数ダイアログボックスを表示します。
このTRIM関数は、余計な空白を削除します。
ワザと空白を付けてあるのでそれを削除する為です。
文字列には、MID関数をネストしていきます。
文字列には、今度は、SUBSTITUTE関数をネストしていきます。
SUBSTITUTE関数は、置き換えをする関数です。
文字列には、B2を入力しますが、オートフィルハンドルを使って数式をコピーしますので、
複合参照で設定しますので、$B2
検索文字列は、REPT関数をネストしていきます。
文字列は” “
繰り返し回数は 50で今回は設定します。
このREPT関数は文字を繰り返します。半角空白を50回繰り返すという処理になります。
では、SUBSTITUTE関数に戻り数式作成を続けています。
置換対象は省略しますので、さらに上のMID関数に戻ります。
開始位置は、文字列を区切りで使っている半角空白の位置のことです。
ここもオートフィルハンドルで数式をコピーすることを考慮して、
COLUMN関数を使っていきますので、COLUMN関数ダイアログボックスを表示します。
参照にはA2を入力します。MID関数ダイアログボックスに戻ります。
そして、開始位置をアレンジします。COLUMN(A2)*50-49 と*50-49というのを追加します。
最後に文字数に50と入力します。
そして、OKボタンをクリックします。オートフィルハンドルを使って数式をコピーしましょう。
コレで完成なのですが、
MID関数のCOLUMN(A2)*50-49 のところが【？】だと思われますので、
何をどうしていたのかを説明します。
C2とD2とE2には、TRIM関数を除いた数式を入れてあります。
C3とD3とE3には、LEN関数でC2 D2 E2の文字数を算出させています。
簡単説明しますと、いくつ半角空白で分岐されていて、
半角空白が何文字目に登場して、
そこから次の半角空白が何文字先なのかを、
確認することを関数を使うと煩雑になりすぎてしまいます。
そこで、半角空白を50個増やした状態にします。
その文字列から、文字＋半角空白=50文字分ずつをとりあえず、ザックリ、抽出させます。
それがC2やD2。E2は残りの文字です。
そうすると、その50文字の中に、分割したい文字が含まれているハズなので、
あとは、TRIM関数を使って、空白を取り除くという処理をしている訳です。
ですので、分割したい文字列が長い場合は、
50のところを100にして、49のところを99にするといいでしょう。
この方法を使えば、文字列を分割することが出来ますが、
やっぱり区切り位置の方が楽ではないかと思いますが、
スキルアップの為に挑戦してみてはいかがでしょうか？
