先頭の「'」(シングルコーテーション)を削除する
セルに「001」と入力すると「1」が入ります。
では、セルに「001」という文字列を入力するには、どうしたらいいでしょう。これには、2つの方法があります。まずは、正攻法から。これが"Excel流"のやり方です。まず「001」と入力したいセルを選択して、[セルの書式設定]ダイアログボックスを開き、[表示形式]タブ[分類]リストで「文字列」を選択します。つまり、セルの表示形式を"文字列"にするわけです。
この状態で「001」と入力すれば、数値の「1」に変換されず、「001」という文字列が入力できます。
もうひとつの方法は、セルの表示形式は変更せず、「001」の先頭にシングルコーテーション(')をつけて入力します。
先頭にシングルコーテーション(')をつけると、文字列形式で入力できるというのは、かなり多くの方が知っている操作です。では、このシングルコーテーション(')とは何なのでしょう。
Lotus 1-2-3の名残
昔々、まだパソコンがMS-DOSというOSで動いていた頃、今のExcelのように一世を風靡した表計算ソフトがありました。それが、Lotus 1-2-3です。私もよく使いました。てか、私のライターデビューは1-2-3のマクロに関しての記事でした。時は流れて、MicrosoftがWindowsを公開します。そのときMicrosoftは、Excelという表計算ソフトも発売しました。ちなみに、ExcelのVer1.0はMacintosh版です。つまり、Excelを発売した当初は、世界中で「表計算ソフトと言えば1-2-3だよね～」という状況だったわけです。Excelとしては、そうした1-2-3ユーザーに乗り換えて欲しいんです。そうなれば当然、既存の1-2-3データとの互換性はもちろん、1-2-3の機能や操作なども継承しなければなりません。その1-2-3で使われていたのが、シングルコーテーション(')だったんです。
このシングルコーテーション(')のように、データの先頭につける記号を"接頭辞"といいます。1-2-3は、セルに文字列を入力するときは、必ずこのシングルコーテーション(')をつけなければなりませんでした。ちなみに、正確にいうとシングルコーテーション(')は「セル内で左寄せ表示する」という接頭辞です。ほかにも、右寄せにする「"」や中央揃えにする「^」、セルいっぱいに同じ文字を表示する「\」などがありました。Excelが発売された当初、世界中にたくさん存在した既存の1-2-3データでは、セル内の文字列に「'」がついています。また、ユーザーはそうして文字列を入力するという操作に慣れています。そこで、Excelでも「'をつけたら文字列にする」という仕組みを搭載しました。
ただ、この仕組みというのは、あくまで既存1-2-3ユーザーに乗り換えてもらうためのものです。Excelは、それまでの表計算ソフトにはない画期的な仕組みがありました。それが「セルの書式」という考え方です。ですから、Excel流の考え方では、セルに「001」と入力するときは、あらかじめセルの表示形式を文字列にしておくのがセオリーです。「'」で文字列になるというのは、そうした「セルの書式」という概念が存在しなかった、Lotus 1-2-3の名残です。
シングルコーテーション(')の削除
上記のような事情があるので、先頭に入力した「'」って、Excel的にはイレギュラーなものとなっています。たとえば、セルに「'100」と入力されているとき、セル内の文字数を調べると「4」ではなく「3」が返ります。
「'」は、ないものとして扱われます。ですから、LEFT関数などで左端1文字を調べると、「'」ではなく「1」が返ります。「'」はないものとして扱う、というのはVBAでも同じです。
Sub Macro()
MsgBox Len(Range("A1").Value) & vbCrLf & LenB(Range("A1").Value)
End Sub
LENB関数というのは、文字列のバイト数を調べる関数ですが、なぜワークシート関数とVBAの関数で結果が異なるのかというのは、長い話になりますので、ここでは割愛します。歴史的配慮みたいな話です。いずれにしても、先頭の「'」は、ないものとして扱われますので、ワークシート関数やVBAで、先頭の「'」だけを特定して除去することはできないです。
もし、先頭の「'」を削除したいとき、少ないセル数でしたら手で削除するのが早いです。でも、大量に存在していたら大変です。そんなときは、簡単な方法があります。
上図は、セル範囲にA1:A5に「'100」と入力しました。表示形式は「標準」です。まず、どこか別のセルで[書式のコピー/貼り付け]を実行します。
マウスポインタが"ハケ"の形になったら、書式を貼り付けたいセル範囲(ここでは、セル範囲A1:A5)をドラッグします。
実行すると、先頭の「'」が削除されます。
ただし、セル内の配置は左寄せのままです。セルに入力されているのが「'田中」みたいな文字列でしたら、これでいいのですが、今回は数値ですから、ちょっと困ります。このセルは、一度編集状態にして再度入力し直すと、本来の右寄せに戻ります。
先頭の「'」を除去すると同時に、セル内の配置も標準にしたいのでしたら、次のような方法もあります。まずは、古典的なやり方から。どこか、ブランクセルを普通にコピーします。Ctrl + Cなどを押してください。
「'」を除去したいセル範囲を選択し、Ctrl + Alt + Vを押して[形式を選択して貼り付け]ダイアログボックスを開きます。
[演算]グループの[加算]を選択して[OK]ボタンをクリックします。
実行すると、「'」が除去されると同時に、標準の右寄せになります。
ブランクセルは、数値的には0です。0を加算しても数値は変わりません。でも、加算という演算を行ったので、その結果は数値になります。数値ですからExcelが、標準の右寄せにしてくれたということです。
最後に、最も簡単な方法をご紹介します。まず「'」を除去したいセル範囲を選択します。
[データ]タブ[データツール]グループの[区切り位置]をクリックします。
[区切り位置指定ウィザード - 1/3]ダイアログボックスが表示されます。
何もしないで、ただ[完了]ボタンをクリックします。
セルに「001」などを入力したいとき、本来であればセルの表示形式を設定してから入力するのがExcel流の考え方なのですけど、とはいえ、面倒くさいときは私も「'001」って入力するときもあります。おそらく、多くの人がやっていることでしょう。先頭に「'」を入力して文字列扱いすることを全否定はしませんが、その違いを理解していただきたいです。間違っても「セルに文字列として入力する方法は、表示形式と「'」の2通りある」と、同じレベルで認識しないでください。「'」はあくまで、大昔の名残です。Excelの本流ではありません。そして、接頭辞の「'」は、ワークシート関数やVBAから操作できない特殊なものになる、ということも忘れないでください。