エクセルでカンマ区切りのままつながった文字列の分割

JSONファイルを開いたら
[] で区切られている文字のファイルでした。
エクセルにまとめたいのですがうまくいきません。
文字列は全部つながっている状態でセルごとに行、列、に分けてコピーすることができません。
膨大な量です

["2018-06-18T21:41:51", "abc"], ["2018-06-18T21:40:15", "def"], ["2018-06-18T20:59:40", "ghi"],
というような感じでアルファベットで示している部分にユーザー名が入ります。
できればユーザー名ごとにまとめたり、ユーザー名の出現回数や日時などまとめたいです。

EXCELのデータタブ、区切り位置ではうまく区切れないため
何か良い方法をご存じの方がいらっしゃいましたらお願いいたします。

よろしくお願いいたします。

No.4 ベストアンサー 回答者： googoo900 回答日時： 2019/06/26 01:38

完成形が提示されていないので、何とも言えませんが、自分ならこうするというやり方を紹介します。

手順は長いように見えますが、時間はかかりません。
日時の列は表示形式の変更が必要になるかもしれません。

◆完成イメージ
年月日 時分秒_________ユーザー名
2018-06-18 21:41:51__abc
2018-06-18 21:40:15__def
2018-06-18 20:59:40__ghi
※アンダーバーは配置崩れ防止のために便宜上入力しています。

この完成イメージにしておけば、ユーザー名ごとにまとめたり、ユーザー名の出現回数や日時などまとめるのも、並べ替えやフィルターを使えば容易にできます。

◆手順
１．データがあるセル(列)を選択して、メニュー ⇒ データ ⇒ 区切り位置 ⇒ 『カンマやタブなどの・・・』を選択して次へ ⇒ 『区切り文字』のところにその他のみチェックをつけて右側の入力欄に　]　を入力して『完了』をクリック
２．行方向にデータが分割されたので、その行を選択してコピー ⇒ 別のシートのA1セルを選択して行列を入れ替えて貼付け(A1セルでなくても任意のセルでよいです)
３．貼り付けたデータがある列を選択して、CTRL+H(置換)
４．検索する文字列に『*[』、置換後の文字列に何も入力せずに、『すべて置換』をクリック
５．置換の画面そのままで、検索する文字列に『"』、置換後の文字列に何も入力せずに、『すべて置換』をクリック
６．列選択はそのままで、メニュー ⇒ データ ⇒ 区切り位置 ⇒ 『カンマやタブなどの・・・』を選択して次へ ⇒ 『カンマ』のみにチェックをつけて『完了』をクリック
７．日付時刻のある列を選択して、、CTRL+H(置換)
８．検索する文字列に『T』、置換後の文字列に『 』(半角スペース)を入力して、『すべて置換』をクリック
これで上記の完成イメージの配置になったはずです。

この回答へのお礼

ありがとうございます。求めていた答えにたどりつきました。感謝します。ご教授いただきありがとうございます。

お礼日時：2019/06/26 19:45

No.6 回答者： d-q-t-p 回答日時： 2019/06/26 10:36

#1です。

> 1行目が横に果てしなく長くなってしまいました。
PowerQueryの[変換]で行列を入れ替えできます。

どうしても数式でというなら A1にデータがあるものとして

=IFERROR(TEXT(INDEX(FILTERXML(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("<a>"&A$1&"</a>","[""","<b>"),""", ""","</b><c>"),"""]","</c>"),"//b"),ROW(A1)),"yyyy-mm-ddThh:mm:ss"),"")

=IFERROR(TEXT(INDEX(FILTERXML(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("<a>"&A$1&"</a>","[""","<b>"),""", ""","</b><c>"),"""]","</c>"),"//c"),ROW(A1)),"yyyy-mm-ddThh:mm:ss"),"")

をそれぞれ下方向にオートフィルコピーでどうでしょう。

この回答へのお礼

ご教授いただきありがとうございます。

お礼日時：2019/06/26 19:45

No.5 回答者： usa3usa 回答日時： 2019/06/26 08:42

＞何か良い方法をご存じの方がいらっしゃいましたらお願いいたします。

私なら
フリーソフトのmeryを起動する
https://www.vector.co.jp/soft/winnt/writing/se47 …

今回問題となっている　[] で区切られている文字のファイル　を開く
CTRL-R　を押す
画面の、検索する文字列[i]　に
　\], \[
置換後の文字列[p]に
　\n
正規表現を使用する[x]にチェックを入れて
「すべて置換」のボタンを押します

この回答へのお礼

ご教授いただきありがとうございます。

お礼日時：2019/06/26 19:45

No.3 回答者： komugi3333 回答日時： 2019/06/25 22:20

あ、補足ですが、テキストエディタは「TeraPad」というフリーウェアが、(01)も(02)も簡単にやりやすいです（特にTAB記号への変換が。

また、1度でも使った置換文字列は自動的にドロップダウンのリストとして、保存してくれますし）。

もちろんWordでもできますが、過去の置換文字列を自動保存してドロップダウンリスト化くれないので面倒くさいです。

この回答へのお礼

ご教授いただきありがとうございます。

お礼日時：2019/06/26 19:45

No.2 回答者： komugi3333 回答日時： 2019/06/25 22:13

テキストエディタやWordなどで・・・

(01)特定の文字列を改行コードに置換する方法
(02)特定の文字列を特定の文字（カンマ1文字など）に置換する方法

・・・をWeb検索して、その方法を調べてみてください。


(01)を利用すれば、　"], ["　の位置で改行できます。
　なお、Wordでは段落記号に置換することになります。

(02)では、", " をカンマひと文字、あるいはタブ記号に置換できます。
　ちなみにですが、タブ記号に置換すると、そのテキストをExcelにコピペ
　したときに、タブ記号がセルとセルの区切りとして貼り付きます。
　これはどのExcelでもそういう仕様です。
　なので僕はカンマ区切りのCSVはいつも、
　カンマをTAB記号に一括置換してしまいます。
　そうしておけば、いつでもExcelに、セル区切りで貼り付くので・・・。

先頭の　["　や、 最後尾の　"]　などが残ると思いますので、それは手作業で消します。

最終的には、Jsonファイルの問題のテキストを、テキストエディタやWordで
「日時」と「ユーザー名」の列に分かれたリスト形式の表データにすることができます。

そうできれば、あとはピボットテーブルなどで、どのユーザー名が何件あるかを調べればOKだと思います。

この回答へのお礼

ご教授いただきありがとうございます。

お礼日時：2019/06/26 19:45

No.1 回答者： d-q-t-p 回答日時： 2019/06/25 22:00

必要な情報が提示されていないので具体的な回答はできかねますけど

PowerQueryで読み込んで加工したらどうですか？
https://qiita.com/afukuma/items/65c6e96bd15b319e …

この回答へのお礼

ありがとうございます。一人ずつ区切ることはできましたが
1行目が横に果てしなく長くなってしまいました。
どうやって縦になおしてまとめたらいいかよくわかりません。

お礼日時：2019/06/25 22:15