文字列を分割したい（Excel編）

2022年2月11日 11:27

社員から色々と相談を受けます。

社員「スラッシュ（／）で文字列を繋げてあってさー、これを１つ１つに分けたいんだけど、何か良い方法はない？」

はて？？
詳しく聞いてみると、こんな内容でした。
例えば、「山田／男／北海道／10,000円／対象外」のように、何かの情報を記号の／で繋げてあるデータがあり、これをバラバラに分けたいとのことでした。なるほどねー
そこで考えた方法としては、／を　.（カンマ）に置換して、CSVデータにしてしまえば良いかとも思ったのですが、金額の3桁区切りの　.（カンマ）等があったりしたので、それは出来なさそうでした。

Excelの関数で何とかなるかな

こんなExcelを作成して、解決できました。

解説します

まずは、区切り文字をセルB1に記入しています。区切り文字が今回は／ですが、他の可能性もあるので、何でもいけるようにしています。

そして、セルA5に区切りたい文字列を張り付けると、セルB5～H5に何文字目を取り出すか計算しています。今回は、／が6個まで対応できるようにしていますが、必要であればもっと増やすことも可能です。

さてさて、上記の①②はヨシとして、

まず、青色の列(B列～H列)の「分割位置」ですが、「／」が何文字目にあるかを特定しています。B列の③は数字の0（ゼロ）を記入しているだけです。

【④の解説】
④は、=IFERROR(SEARCH($B$1,$A5,B5+1),LEN($A5)+1)　となっています。
途中にある　SEARCH($B$1,$A5,B5+1)　からいってみましょう。
$B$1は入力した①の「／」ですね。$A5は②の対象の文字列、B5+1は③の0に+1なので1。つまり、SEARCH("／","山田／男／東京／10,000円／対象外",1)と言う事になりす。
"山田／男／東京／10,000円／対象外"の1文字目から数えて"／"は何文字目にありますか？と言う関数になっていて、答えは3と言う結果が返ってきます。
それを、IFERROR関数で囲んでいますので、IFERROR(3,LEN($A5)+1)となりす。LEN($A5)は、②の"山田／男／東京／10,000円／対象外"の文字列の長さ（LEN関数）なので、19が返ってきます。つまり、IFERROR(3,20)となりますね。
IFERROR関数は、IFERROR(A,B)でAの部分にエラーがあった場合にBと表示する関数です。今回の場合、3はエラーでない結果が返ってきているので、IFERROR(3,20)の結果も3となります。このIFERRORは後の⑦のところで意味があるので、ここではスルーします。
長くなりましたが、④の=IFERROR(SEARCH($B$1,$A5,B5+1),LEN($A5)+1)　の結果は3ですね！最初の「／」が3文字目にありましたと言う計算式です。

【⑤⑥の解説】
続いて、⑤の　=IFERROR(SEARCH($B$1,$A5,C5+1),LEN($A5)+1)　ですが、④との違いは、途中にあるC5だけです！つまり、C5は先ほどの結果の「3」（一つ左隣りのセル）なので、SEARCH($B$1,$A5,C5+1)は、SEARCH("／","山田／男／東京／10,000円／対象外",4)と言う事になりす。
"山田／男／東京／10,000円／対象外"の4文字目以降で"／"は何文字目にありますか？と言う関数になっていて、答えは5と言う結果が返ってきます。
同様に⑥の　=IFERROR(SEARCH($B$1,$A5,D5+1),LEN($A5)+1)　も途中のD5しか変わらないので、"山田／男／東京／10,000円／対象外"の6文字目以降で"／"は何文字目にありますか？と言う関数になっていて、答えは8と言う結果が返ってきます。

【⑦の解説】
続いて、⑦ですが、式としてはこれまでの法則と変わらず、=IFERROR(SEARCH($B$1,$A5,G5+1),LEN($A5)+1)　と、左となりのセルの値を参照して結果を計算しています。SEARCH($B$1,$A5,G5+1)は、SEARCH("／","山田／男／東京／10,000円／対象外",21)　なので、"山田／男／東京／10,000円／対象外"の21文字目以降に"／"は何文字目にありますか？と言う関数になっています。
ただ、「21文字目以降」との事ですが、「山田／男／東京／10,000円／対象外」は19文字しかないので、結果は「#VALUE!」と言うエラー値が返ってきます。ここでIFERROR関数の登場です。
IFERROR関数は、IFERROR(A,B)のAの部分でエラーがあった場合にBと表示する関数です。⑦は、=IFERROR(#VALUE!,LEN($A5)+1)となるため、IFERROR(A,B)のAがエラーなので、Bの部分である　LEN($A5)+1　が表示されます。LEN($A5)は先ほど19文字なので、+1されて20となります。
⑦の左側のセルG5も20のように、「／」が見つからない時は、文字列の長さ19文字＋1で20を表示しています。

続いて、緑色の列(I列～N列)の「結果」ですが、先ほど調べた「／」が何文字目にあるかを使って、分割した文字を表示しています。

【⑧の解説】
⑧は、=IFERROR(MID($A5,B5+1,C5-B5-1),"")となっています。途中にあるMID($A5,B5+1,C5-B5-1)ですが、$A5は②の対象の文字列、B5+1は③の0に+1なので1。C5-B5-1は、C5は「／」を見つけた3文字目の3、B5は③の0、そして-1なので、C5-B5-1は2です。
つまり、MID("山田／男／東京／10,000円／対象外",1,2)となり、1文字目から2文字を取り出すと「山田」と言う結果が返ってきます。
IFERROR関数は、IFERROR(A,B)でAの部分がエラーではないので、そのまま「山田」となりますね。

【⑨の解説】
続いて、⑨の　=IFERROR(MID($A5,C5+1,D5-C5-1),"")　ですが、⑧との違いは、MID関数の開始位置と終了位置が変わっていますね。C5は1つ目の「／」の位置の3文字目、D5は2つ目の「／」の位置の5文字目です。これらを組み合わせると、MID("山田／男／東京／10,000円／対象外",4,1)となり、4文字目から1文字を取り出すと「男」と言う結果が返ってきます。
IFERROR関数は、IFERROR(A,B)でAの部分がエラーではないので、そのまま「男」です。

【⑩の解説】
そして、⑩は、式としては⑧⑨と法則は変わらないのですが、=IFERROR(MID($A5,G5+1,H5-G5-1),"")　を見たときに、MID($A5,G5+1,H5-G5-1)　の箇所はMID("山田／男／東京／10,000円／対象外",21,-1)　となり、21文字目から-1文字を取り出すと言う式になります。-1文字を取り出す事ができないため、結果は「#VALUE!」と言うエラー値が返ってきます。
今回のIFERROR関数は、IFERROR(数式の結果,"")としているので、IFERROR(#VALUE!,"")となり、””（空白）が結果として返ってくる形です。

⑪～⑬のように、空白や少数点、＼や/ も正しく識別できています。
これで社員の要望には応えられたかな。

区切り位置指定ウィザードで一発（泣）

社員に「できましたよー」って誇らしげに説明して、その時は自画自賛していたのですが、ふとExcelの調べものの時に「区切り位置指定ウィザード」で簡単にできる事を発見(T-T)
確かに、区切り文字がカンマやタブの時に使った事はあったけど、区切り文字が何でも対応しているなんて。。。
誰かが言ってた「無知の知」じゃなく「無知の恥」。
絶対こっちを使った方が楽です！！