今日を乗り切るExcel研究所

Excel に働かされていませんか

CSV データをExcel シート に貼り付けたい

クリップボードから Excel に CSV テキストを貼り付ける方法と、貼り付けの区切り文字をカンマに切り替えるマクロを検討します。

f:id:shego:20170531141512p:plain

テキストデータの貼り付け

セルのデータをいったんテキストにして、テキストエディタの高度な編集機能で編集・加工することはよくあります。

Excel からコピーしたデータはこの時、セル値の「タブ区切り」となり、テキスト編集後はそのまま、シートに貼り付るだけで戻せます。

ところで、手元にあるテキストデータが CSV 形式で、これをそのまま Excel シートに貼り付けたいこともあります。

これも TAB と同様に「カンマ区切り」で分割してシートに貼り付ける事はできないものでしょうか。

手作業で CSV データを貼り付けるには

CSV データを貼り付ける方法について調べてみました。

まず、テキスト貼り付け時の区切り文字を変更する設定そのものは、Excel に存在しないようです。

しかし、区切り文字を変更させることは可能です。

「区切り位置」コマンドの「区切り位置ウィザード」で「区切り文字」を指定できますが、これが貼り付け時の区切り文字にも影響を与えます。

「区切り位置ウィザード」の「区切り文字」で 、デフォルトの「タブ」を「カンマ」に変更して一度完了をすると、以降、テキストの貼り付けでもカンマが区切り文字として使用されます。

どうも、直近で使用した「区切り位置ウィザード」ダイアログでの指定内容が、繰り返し作業の負担を軽減するためか Excel のどこかに保持されていて、テキストの貼り付けでもそれが流用されるということのようです。

ただ残念なことに、セルを「コピー」したときの方の区切り文字は関係ないようで、相変わらず「タブ区切り」のままです。

テキストの貼り付けに影響するのは「区切り位置ウィザード」の2ページ目の内容で、テキストの貼り付けだけでなく「テキストファイルウィザード」の初期状態に影響を与えます。

【手順例】

  1. 適当に、値のあるセルを選択します
    • たとえば A1 セルに文字"a"を入力しておき、選択状態にします
  2. 「区切り位置ウィザード」を開きます
    • キー操作: AltAE
  3. 1ページ目(1/3)の「元データの形式」で「カンマやタブなどの区切り文字によって~」を選択し(デフォルトのまま)「次へ」ボタンを押します。
  4. 2ページ目(2/3)の「区切り文字」ボックスで「タブ」のチェックを外し、「カンマ」のみチェックが入った状態にします
    • 「連続した区切り文字は~」のチェックは外した方がいいでしょう
    • 「文字列の引用符」はダブルクオート(“)のままでOKです f:id:shego:20170523121658p:plain
  5. 「完了」ボタンを押します(3ページ目は不要)

これで以降、CSV が貼り付けられるようになります。

テキストエディタから CSV データを Excel にコピー&ペーストして見てください。「カンマ区切り」でフィールドデータがセルに入力される状態になっているはずです。

「タブ区切り」に戻すには上記手順で「タブ」のみにチェックが入っている状態に設定します。

また、Excelを終了するとその設定は失われ、次回起動時にはタブ区切りに戻ります。

 



テキストの区切り文字を切り替えるマクロ

TAB とカンマで区切り文字を切り替えるマクロを作成しました。

本マクロ VBA を実行すると、テキストの区切り文字が TAB からカンマ(“,”)に変更され、CSVテキストの貼り付けで、各フィールドがセルに分割されます。

以降、同マクロを実行する度に、TABとカンマで区切り文字が切り替わります。

(「カンマ区切り」で「コピー」もできてほしいという要望もありますが、それはまた別の課題とします)

Option Explicit

Sub 区切り文字の切り替え_タブ⇔カンマ()
    Static useCSV As Boolean
    useCSV = Not useCSV
    
    Dim c As Range
    Set c = ActiveSheet.UsedRange.Rows(1)
    Set c = c.Cells(1, c.Cells.Count + 1)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
        
    c.Value = "a"
    c.TextToColumns _
        Tab:=Not useCSV, _
        Comma:=useCSV, _
        ConsecutiveDelimiter:=False, _
        TextQualifier:=xlTextQualifierDoubleQuote
    c.Clear
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

【使い方】

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします
  2. 本マクロを実行します
  3. クリップボードにコピーしたカンマ区切りのテキストを、シートに「貼り付け」します
  4. CSVの各値がセルに入力されます
  5. マクロを実行するたびに、貼り付けの区切り文字が TAB とカンマで切り替わります

【注意】 本マクロの実行後の状態は、「区切り位置ウィザード」と「テキストファイルウィザード」の、2ページ目の初期状態にも反映されます。

まとめ

CSV のカンマ区切りで分割するように、テキスト貼り付けの区切り文字の変更するには、「区切り位置ウィザード」で「区切り文字」にカンマを設定します。

本記事の内容は、Windows 10 の Excel 2013 で動作確認しました。