記事の概要
Redash を使ってデータを抽出する業務に携わるようになったのですが、抽出の依頼をする人によって元データの形式が異なっていました。
加工せずに済むので'Python','AWS','AI','初心者','JavaScript' という形式でほしいのですが、例えばPython,AWS,AI,初心者,JavaScript や Python、AWS、AI、初心者、JavaScriptで依頼が来るといった具合です。
依頼者側もいちいち手作業で上記の形式にするのは面倒だと思うので、シンプルな関数で解決することにしました。
⓪SUBSTITUTE関数について
今回使うSUBSTITUTE関数は、このような関数です。
概要 : 文字列内の既存のテキストを新しいテキストに置き換えます。
構文 : =SUBSTITUTE(検索対象のテキスト, 検索文字列, 置換文字列, [出現回数])
「出現回数」は省略することが多いのですが、こういった使い方もできます。
A2のadvent calendarには小文字のaが複数ありますが、1回目のaのみを大文字のAに置き換えています。
①全体の,を','に置き換える
まず、=SUBSTITUTE(A2,",","','")で、A2の文字列の,をすべて ','に変換します。
これだけだと、先頭と末尾には'がつきません。
②&で先頭と末尾に'をつける
今回は文字列と文字列の結合に&を使っていますが、&を使いたくない場合はCONCATENATE関数を使用してください。
GASのconcatメソッドでは3つ以上できますが、スプレッドシートのCONCAT関数で連結できる値は2つのみのため、スプレッドシートではあまり使い勝手が良くない印象です。
さきほどの=SUBSTITUTE(A2,",","','")を、="'"&SUBSTITUTE(A2,",","','")&"'" と書き足すと、このような結果になります。
これで完成でも良いと思いますが、空白を削除したい場合もあるかと思います。
③空白がある場合は
A2の「初心者」の後ろに半角スペースを入れてみました。
="'"&SUBSTITUTE(SUBSTITUTE(A2,",","','")," ","")&"'"のように追いSUBSTITUTEで十分かと思います。
空白を削除できる関数はいろいろとあるので、お好みの関数をご利用ください。
Excelですが、下記の記事でも空白を削除するための関数をいくつか紹介しています。
④、や他の区切り文字で区切られている場合も
="'"&SUBSTITUTE(SUBSTITUTE(A3,"、","','")," ","")&"'"や="'"&SUBSTITUTE(SUBSTITUTE(A4,"/","','")," ","")&"'"などと、検索文字列を書き換えてご利用ください。
さらにIF関数やCOUNTIF関数と組み合わせると、より多くの場面で利用できると思います。