Office TANAKA - Excel Tips[先頭の「'」(シングルコーテーション)を削除する]

Accessなど他のソフトで作成したデータをExcelで読み込むと、データの先頭に「'」(シングルコーテーション)が付加されることがあります。ここでは、何らかの理由で付いてしまった「'」を除去する方法をご紹介します。

「'」とは何か？

まず「'」とは何なのかを理解しておきましょう。
「'」は接頭辞と呼ばれる特殊な記号の一種です。接頭辞とはデータの先頭に付ける記号のことで、記号の後ろに入力したデータの配置や種類などを変更するために使います。

もともと、この接頭辞という機能はMS-DOS版のLotus 1-2-3で活躍したテクニックでした。当時の1-2-3には「'」だけでなく、データを中央揃えの文字列として表示する「^」、同じく右揃えで表示する「"」や、データをセル幅いっぱいに繰り返し表示する「\」などもありました。ちなみに「'」は左揃えの文字列として表示する接頭辞です。

この1-2-3の接頭辞を体験したい方は、Excelの[オプション]ダイアログボックスを開き[移行]タブの[Lotus 1-2-3形式のキー操作]チェックボックスをオンにしてください。実行すると次のように操作できます。

[Lotus 1-2-3形式のキー操作]がオンの状態

もし実際に[Lotus 1-2-3形式のキー操作]チェックボックスをオンにして上記の入力を試した方は、確認後にチェックボックスをオフに戻してください。この後の解説では、この[Lotus 1-2-3形式のキー操作]チェックボックスがオフになっているという前提で話を進めます。

Excelや1-2-3などの表計算ソフトは、セルに入力された数字は計算可能な「数値データ」として扱います。そうでなければ困ります。しかし、ときには数値を「文字列データ」として扱いたいときもあります。そんなときに重宝するのがこうした接頭辞なのです。現在のExcelで「'」が左揃え文字列の接頭辞として使えるのは、MS-DOS版1-2-3の仕様を引き継いだためです。ここで重要なことは、「'」などの接頭辞は特殊な記号でありデータではないということです。

「'」が勝手に付くケース

まず、[Lotus 1-2-3形式のキー操作]チェックボックスがオフになっているのを確認してください。このチェックボックスは、Excelを標準インストールした初期状態ではオフになっています。この標準状態で、セルにいくつかのデータを入力してみます。なお、ここでは入力の確定にCtrlキー＋Enterキーを押して、入力完了後にアクティブセルが移動しないように操作しています。

[Lotus 1-2-3形式のキー操作]がオフの状態

標準状態では、数字は数字、文字列は文字列としてセルに入力されます。文字列を入力したからといって、勝手に「'」が付加されることはありません。また、明示的に「'」を入力した場合は、データの先頭に「'」が付いていることも数式バーからわかります。このように、Excelの標準状態では「'」が勝手に付加されることはありません。では、今度は[Lotus 1-2-3形式のキー操作]チェックボックスをオンにして試してみましょう。

[Lotus 1-2-3形式のキー操作]がオンの状態

上の画像をよくご覧ください。セルB2は「tanaka」という文字列を「'」を付けずに入力しているにもかかわらず、入力確定後は自動的に「'」が付いています。さらに、セルB3やセルB4のように、明示的に「'」を付けて入力したデータでは、先頭に「'」が1つだけ付加されています。これが、MS-DOS版1-2-3のルールなのです。

実はMS-DOS版1-2-3では、セルに文字列を入力するときは必ず接頭辞を付けるという決まりがありました。しかし、文字列を入力するたびに毎回必ず接頭辞を入力するのは面倒です。そこで、接頭辞を付けずに入力された文字列には、1-2-3が自動的に、文字列の標準接頭辞である「'」を付加してくれたのです。セルB3やセルB4のように、もしユーザーが明示的に「'」を入力した場合は、1-2-3の自動付加機能は働きません。したがって、セルB2の「tanaka」は自動付加機能によって「'tanaka」となり、セルB4の「'tanaka」は接頭辞を省略せずに入力していますのでそのまま「'tanaka」となります。この自動的に付加された「'」と、ユーザーが自ら入力した「'」は、次の解説で重要な違いが生じてきます。

自動的に付いた「'」を削除する

ここまでの確認で次のことがわかりました。

Excelの標準状態では、勝手に「'」が付加されることはない
標準状態でも、ユーザーが自ら入力した場合は「'」が付加される
[Lotus 1-2-3形式のキー操作]をオンにすると、文字列に自動的に「'」が付加される
[Lotus 1-2-3形式のキー操作]がオンでも、ユーザーが自ら「'」を入力した場合は自動的に付加されない

このうち、3.のケースで自動付加された「'」を削除するのは簡単です。ユーザーが自ら「'」を入力しないのに「'」が付くのは[Lotus 1-2-3形式のキー操作]チェックボックスがオンになっているからです。そこで、[Lotus 1-2-3形式のキー操作]チェックボックスをオフにすることで、[Lotus 1-2-3形式のキー操作]チェックボックスがオンのとき自動付加された「'」を一気に消し去ることができます。

問題は、その他のケースです。何らかの理由でユーザーが自ら「'」を入力したり、Accessなど他のソフトが出力したデータで、勝手に付いた「'」は[Lotus 1-2-3形式のキー操作]をオフにしても削除されません。

自ら入力した「'」を削除する

多くのユーザーが悩まされているのは、こちらのケースです。
冒頭でも書きましたが、セル内のデータを文字列として扱う接頭辞の「'」はデータではありません。したがって、関数やVBAで「'」だけを認識することはできません。実際にやってみましょう。下の図は、セルA1に「'tanaka」というデータを入力し、セルB1に＝LEN(A1)という数式を入力しました。LEN関数は、引数に指定したセル内に入力されているデータの文字数を返します。

LEN関数では先頭の「'」がカウントされません。VBAではどうでしょう。次のコードは、セルA1に入力されている文字列のうち、最初の1文字を画面に表示します。

Sub test()
    Dim tmp As String
    tmp = Range("A1").Text
    MsgBox Left(tmp, 1)
End Sub

実行結果は次の通りです。こちらも、先頭文字は「t」と認識されてしまいます。

「'」を削除するには次のようにします。なぜ削除できるかを解説すると長くなりますので、とりあえずやり方だけ覚えてください。ここではセルA1に入力した「'tanaka」から「'」を削除しますが、同じ手順で複数のセルを同時に処理することも可能です。

任意のセルに数字の0を入力します。
その0をコピーします。
「'」を削除したいセルを選択します。
[編集]-[形式を選択して貼り付け]を実行します。
[演算]グループの[加算]チェックボックスをオンにします。
[OK]ボタンをクリックします。
Escキーを押してカットコピーモードを解除します。

あるいは、VBAで処理するのでしたら次のコードをお使いください「'」を削除したいセルを選択してマクロを実行すると、すべてのセルで先頭の「'」を削除します。

Sub Sample()
  Dim c As Range
  For Each c In Selection
      If Not c.HasFormula Then c = c
  Next c
End Sub