Excelでセルの先頭にアポストロフィが付いてしまい、数値が文字列として扱われたり、見た目には表示されない記号を消せなかったりして困ることがあります。
アポストロフィは、セルへ入力する値を文字列として扱わせるための記号です。
入力用のアポストロフィと、セル内に実際の文字として保存されたアポストロフィは、見た目が似ていても対処方法が異なります。
この記事で確認するポイント
・セル先頭にアポストロフィを入力して文字列にする方法
・表示されないアポストロフィと表示されるアポストロフィの違い
・置換、数式、区切り位置を使って削除する方法
サンプルデータでは、1行目を見出し行として、A列に元データ、B列に処理後の値を入力するものとします。
それでは、アポストロフィの扱いを順番に確認していきましょう。
エクセルでセル先頭のアポストロフィを入力する方法
それではまず、セル先頭のアポストロフィを入力して、値を文字列として扱う方法について解説していきます。
| A列 | B列 |
|---|---|
| 商品コード | 入力結果 |
| 00125 | 00125 |
先頭へ半角アポストロフィを付ける入力操作
セルに入力する直前に、キーボードで半角のアポストロフィを1文字入力します。
たとえばA2セルへ00125と表示したいときは、‘00125と入力してEnterキーを押します。
入力後のセルには00125だけが表示され、先頭のアポストロフィは通常表示されません。
これは入力した値を数値ではなく文字列として保存するための指定です。
郵便番号、社員番号、商品コード、口座番号のように、先頭のゼロを残したいデータで役立ちます。
アポストロフィを先頭に付けて入力すると、00125は数値の125ではなく、文字列の00125として扱われます。
セルを編集状態にするF2キーを押すと、入力時に付けたアポストロフィを確認できる場合があります。
ただし、数式バーでは表示のされ方がデータの状態によって異なるため、セルの配置や計算結果も併せて判断しましょう。
先頭ゼロを残す文字列指定の活用場面
Excelは、数字だけで構成された入力内容を自動的に数値として判定します。
そのため00125をそのまま入力すると、表示は125になり、先頭のゼロが消えることがあります。
コード類は計算対象ではなく識別情報であることが多いため、文字列化する意識が重要です。
電話番号の03-1234-5678、郵便番号の100-0001、日付のように見える4-1といった内容も、意図しない変換を避けたい場面があります。
既に列全体を文字列形式に設定している場合はアポストロフィなしでも入力できますが、個別のセルだけを確実に文字列にしたいときは先頭記号が便利です。
ただし、文字列化した値はSUM関数などの集計で数値として加算されません。
数量や金額にアポストロフィを付ける場合は、後から計算できるかを確認する必要があります。
アポストロフィを文字として表示する入力方法
入力用のアポストロフィではなく、記号そのものをセルに表示したい場合もあります。
この場合は、先頭にアポストロフィを2つ続けて入力します。
たとえばセルに’ABCと表示するには、”ABCと入力します。
最初の1文字目が文字列指定として使われ、2文字目がセル内に表示されるアポストロフィになります。
入力値 ”ABC
セルの表示 ’ABC
意味 先頭の1文字目は文字列指定、2文字目は表示用の記号です。
CSVから取り込んだデータにアポストロフィが見える場合は、このように実データとして保存されている可能性があります。
見えない記号を消したいのか、表示されている記号を削除したいのかを区別することが、正しい処理への近道です。
【操作のポイント】先頭ゼロを保つだけならアポストロフィは1つ、記号自体も表示したいなら2つ入力します。
セルに表示されないアポストロフィの確認方法
続いては、画面上では見えないアポストロフィが入力されているかを確認する方法を見ていきます。
| セルの表示 | 主な状態 |
|---|---|
| 00125 | 文字列指定のアポストロフィが付いている可能性 |
| ‘00125 | アポストロフィが実データとして表示されている可能性 |
数式バーと配置による状態の見分け方
最初に対象セルをクリックし、数式バーとセル内の表示を確認します。
一般設定のセルでは、数値は右寄せ、文字列は左寄せで表示されることが基本です。
そのため00125が左寄せで、計算にも使えない場合は、文字列として保存されている可能性があります。
ただし、セルの配置を手動で変更していると左右の位置だけでは判定できません。
数式バーにアポストロフィが表示されているか、セルの表示形式が文字列か、数式で判定した結果はどうかを組み合わせて確認しましょう。
Excelの緑色の三角マークがセル左上に出ている場合は、数値が文字列として保存されているというエラーチェックの候補でもあります。
この警告は必ずしも誤りではなく、コードとして扱いたいなら問題ないケースもあります。
ISTEXT関数とISNUMBER関数による判定
表示だけでは判定しづらいときは、関数でデータ型を調べる方法が確実です。
B2セルに次の数式を入力します。
=ISTEXT(A2)
結果がTRUEならA2セルは文字列、FALSEなら文字列ではありません。
数値かどうかを確認する場合は、次の数式を使います。
=ISNUMBER(A2)
アポストロフィによって文字列化されている00125は、ISTEXT関数がTRUE、ISNUMBER関数がFALSEになります。
見た目が数字でも内部では文字列という状態を、関数で明確に切り分けられます。
サンプルデータの1行目は見出しなので、数式はB2から入力し、必要に応じて下方向へコピーしてください。
LEN関数で文字数を比較する確認方法
セル内に見えるアポストロフィが含まれているかは、LEN関数で文字数を数える方法でも確認できます。
たとえばA2に’ABCが表示されている場合、次の数式を入力します。
=LEN(A2)
結果が4なら、アポストロフィを含む4文字として保存されています。
一方で、入力用としてだけ使われたアポストロフィは、通常は文字数に含まれません。
ABCと表示されていてLEN関数の結果も3なら、先頭記号は実データではないと判断できます。
インポート後のデータで置換処理をする前に、数件をLEN関数で調べておくと、不要な文字まで消す事故を防げます。
【操作のポイント】見た目だけで決めず、ISTEXT関数、ISNUMBER関数、LEN関数で内部の状態を確認します。
入力用アポストロフィを削除して数値へ戻す方法
続いては、文字列として入力された数値を、計算できる数値へ戻す方法を確認していきます。
| A列の元データ | B列の変換後 |
|---|---|
| ‘1250 | 1250 |
| ‘980 | 980 |
エラー表示から数値へ変換する操作
文字列として保存された数値のセルに緑の三角マークが表示されている場合は、警告メニューから変換できます。
対象セルまたは対象範囲を選択し、セルの近くに表示される警告アイコンをクリックします。
メニューに数値に変換が表示されたら、その項目を選択します。
これで入力用アポストロフィによる文字列指定が解除され、数値として計算に利用できる状態になります。
先頭ゼロが必要なコードまで数値に変換すると、ゼロが消える場合があります。
変換対象が金額、個数、年月など、実際に計算するデータだけであることを確認してから実行しましょう。
区切り位置機能で一括変換する操作
大量の文字列数値を一度に変換したいときは、区切り位置機能が使えます。
対象となるA2からA100などの範囲を選択し、データタブから区切り位置を選びます。
ウィザードの最初の画面では、区切り文字付きのまま次へ進んでも、固定幅のまま次へ進んでも構いません。
区切りを追加せず、そのまま次へ進み、列のデータ形式で標準を選択して完了します。
Excelが文字列の数字を再判定し、数値として変換します。
セルを分割せずに形式だけを変えたい場合にも使えるため、データ整理で覚えておくと便利です。
区切り位置の基本手順
データタブから区切り位置を選択し、列のデータ形式を標準にして完了します。
VALUE関数で別列に数値を作る方法
元データを残したまま数値化するなら、VALUE関数で別列へ変換結果を作成します。
B2セルに次の数式を入力します。
=VALUE(A2)
この数式は、文字列として保存されている数字を数値へ変換します。
A2が’1250という文字列なら、B2には数値の1250が返されます。
数式を下へコピーする場合は、B2セル右下のフィルハンドルを下方向へドラッグするか、ダブルクリックしてオートフィルを実行します。
変換後の値を固定したいときは、B列をコピーして値として貼り付けます。
VALUE関数は数字以外の文字を含むデータではエラーになるため、空白や通貨記号が混ざっていないかを確認してください。
【操作のポイント】計算に使う数字だけを数値へ戻し、コードや郵便番号は文字列のまま保持します。
表示されるアポストロフィを削除する方法
続いては、セル内に文字として表示されているアポストロフィを削除する方法を確認していきます。
| A列の元データ | B列の削除後 |
|---|---|
| ‘東京営業所 | 東京営業所 |
| ‘大阪営業所 | 大阪営業所 |
検索と置換でアポストロフィを一括削除する操作
表示されているアポストロフィを同じ列からまとめて消すなら、検索と置換が便利です。
削除したい範囲を選択してから、CtrlキーとHキーを同時に押します。
検索する文字列へアポストロフィを入力し、置換後の文字列は空欄のままにします。
すべて置換を実行すると、選択範囲に含まれるアポストロフィが削除されます。
ただし、文章の途中にある引用符としてのアポストロフィまで消える可能性があります。
対象がセル先頭だけなのか、セル内のすべての位置なのかを確認し、必要なら先にファイルを保存してから作業しましょう。
【操作のポイント】置換前に対象範囲を選択すると、別の列や見出しまで変更するリスクを減らせます。
SUBSTITUTE関数で先頭記号を取り除く方法
元のデータを残して削除結果を確認したい場合は、SUBSTITUTE関数を使います。
B2セルへ次の数式を入力します。
=SUBSTITUTE(A2,”‘”,””)
この数式は、A2セルにあるすべてのアポストロフィを空文字列へ置き換えます。
たとえばA2が’東京営業所なら、B2には東京営業所と表示されます。
数式中の二重引用符で囲まれた’が検索対象で、後ろの空の二重引用符が置換後の文字です。
関数で処理すると元データを保管できるため、削除結果を確認してから値として確定できます。
ただし、この式も文字列の途中にあるアポストロフィをすべて削除するので、文章データには注意が必要です。
RIGHT関数で先頭の1文字だけを除く方法
先頭に必ずアポストロフィが1文字だけあり、それ以降の文字は残したい場合はRIGHT関数を使えます。
B2セルへ次の数式を入力します。
=RIGHT(A2,LEN(A2)-1)
LEN(A2)でA2の文字数を数え、そこから1を引いた数だけ右側の文字を取り出す式です。
A2が’東京営業所なら、先頭のアポストロフィを除いた東京営業所が返されます。
この方法は先頭の1文字だけを除くため、途中にあるアポストロフィを保持したいときに向いています。
一方で、先頭にアポストロフィがないデータにも式を適用すると、必要な先頭文字まで消えてしまいます。
IF関数を組み合わせ、LEFT(A2,1)で先頭文字を確認してから処理すると安全です。
【操作のポイント】先頭だけを削除したい場合はRIGHT関数、どの位置にある記号も消したい場合はSUBSTITUTE関数を使い分けます。
アポストロフィを含むデータの貼り付けとCSV取り込み
続いては、コピー＆ペーストやCSV取り込みでアポストロフィが発生する場面を確認していきます。
| 取り込み前の例 | 確認する点 |
|---|---|
| ‘00125 | 入力指定か実データか |
| 00125 | 先頭ゼロの維持 |
CSVを直接開くときの自動変換
CSVファイルをExcelで直接開くと、列の内容が自動判定されます。
数字だけのコードは数値に変換され、先頭ゼロが失われることがあります。
また、外部システムが文字列を表すために付けたアポストロフィが、そのまま表示される場合もあります。
CSVは単なる文字列データであり、Excelの表示形式や自動変換とは別の仕組みです。
大切なコード列があるCSVでは、ファイルを直接ダブルクリックするのではなく、データタブからテキストまたはCSVからを選択して取り込む方法が安心です。
取り込み時に列の型を確認し、コード列を文字列として指定すれば、先頭ゼロの消失を抑えられます。
貼り付け前にセルの表示形式を文字列にする方法
Webページや基幹システムからコードを貼り付ける前に、貼り付け先の列を文字列形式に設定する方法もあります。
対象列を選択し、ホームタブの表示形式から文字列を選びます。
その後で貼り付ければ、00125のような値を文字列として受け入れやすくなります。
ただし、既に貼り付けた値へ表示形式を変更するだけでは、数値から文字列へ期待通り変わらないことがあります。
既存データでは、再入力、区切り位置、VALUE関数などを使って目的に合わせて処理しましょう。
事前に文字列形式へ設定するのは、商品コードや管理番号を大量に貼り付ける前の予防策です。
数値と文字列を混在させないデータ管理
同じ列に数値と文字列が混在すると、並べ替え、検索、集計、VLOOKUP関数やXLOOKUP関数で思わぬ結果になることがあります。
たとえば文字列の00125と数値の125は、見た目が近くても完全には同じ値ではありません。
検索で見つからない場合は、検索値と検索範囲のどちらが文字列かを確認してください。
コード列は文字列、金額列と数量列は数値というように、列ごとの役割を最初に決めることが大切です。
入力規則やテーブル機能を利用すると、入力ルールを共有しやすくなります。
【操作のポイント】CSVや貼り付けの前に列のデータ型を決めると、後からアポストロフィを削除する作業を減らせます。
アポストロフィ削除後に確認したい計算と表示形式
続いては、アポストロフィを削除または変換した後に確認したい計算と表示形式を見ていきます。
| A列の値 | B列の確認式 |
|---|---|
| 1250 | =A2*10 |
四則演算で数値に戻ったか確認する方法
変換後のセルが本当に数値になったかは、簡単な計算式で確認できます。
B2セルに次の数式を入力します。
=A2*10
A2が数値の1250なら、B2には12500が表示されます。
文字列のままでは計算エラーになったり、関数によっては意図しない結果になったりします。
SUM関数で合計が0になる、平均値が合わないといった場合も、アポストロフィ付きの文字列が混ざっているかもしれません。
変換後は計算結果まで確認することで、データ型の不一致を早期に見つけられます。
表示形式で先頭ゼロを保つ方法
数値として計算しながら、見た目だけ先頭ゼロを付けたい場面では、アポストロフィではなくユーザー定義の表示形式が向いています。
たとえば5桁の番号として表示したい場合は、セルの書式設定で表示形式をユーザー定義にし、次の形式を設定します。
00000
数値の125を入力すると、セルには00125と表示されます。
内部の値は125のままなので、計算や並べ替えにも利用できます。
ただし、この方法は桁数が固定の番号に適しており、実際の文字列コードを完全に表現するものではありません。
英字を含むコードや桁数が変わる識別子は、文字列として管理するほうが自然です。
検索関数で一致しない場合の対処
VLOOKUP関数やXLOOKUP関数で値が一致しない場合、片方が数値、もう片方が文字列になっていることがあります。
検索値が数値ならVALUE関数で変換し、検索範囲が文字列ならTEXT関数で形式を合わせる考え方が有効です。
5桁のコードとして文字列化する例は次のとおりです。
=TEXT(A2,”00000″)
この数式は数値125を文字列の00125へ変換します。
検索する側と検索される側で桁数、先頭ゼロ、文字列か数値かを統一しましょう。
【操作のポイント】計算用の数値と表示用のゼロ埋めは、アポストロフィではなく表示形式やTEXT関数で分けて管理します。
まとめ エクセルでセル先頭のアポストロフィを削除・入力・表示する方法
Excelでセル先頭のアポストロフィを扱うときは、まずその記号が入力用なのか、実際に表示される文字なのかを確認することが重要です。
先頭にアポストロフィを1つ付けて入力すると、数値のような値でも文字列として保存できます。
先頭ゼロを残したいコード類では便利ですが、金額や数量のように計算する値には注意が必要です。
文字列数値を数値へ戻すときは、警告アイコンの数値に変換、区切り位置、VALUE関数を使い分けます。
セルに表示されるアポストロフィを消す場合は、検索と置換、SUBSTITUTE関数、RIGHT関数が役立ちます。
CSVの取り込みや貼り付けの前に列の形式を決め、コードは文字列、計算値は数値として統一しましょう。
表示だけ先頭ゼロを付けたい場合には、00000のようなユーザー定義表示形式を使う方法も有効です。
アポストロフィの役割を理解して、入力、表示、削除、計算を意図どおりに管理していきましょう。
