ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.019

先頭のシングル クォーテーション、アポストロフィー(')を取るには(関数編) 《SUBSTITUTE》

2003/2007/2010/2013

データの先頭にシングル クォーテーション(以下クォーテーション)を入れて数値を入力すると文字列として入力することができます。
先頭のクォーテーションは、文字列として入力するために意図的に使う場合もありますし、データをダウンロードしたときに入ってくる場合もあります。

大量データの先頭のクォーテーションを効率的にはずしたい、というお問い合わせです。
残念ながら、[置換]機能によっては外すことはできません。

今回は、関数によって取り外す方法をご紹介します。
データ内の文字列を置換する関数、SUBSTITUTE関数を利用します。

A列セルA2よりクォーテーションが入ったデータがあるとします。

関数を挿入するための列を1列用意し、挿入した列にSUBSTITUTE関数(関数の分類: 文字列操作)を挿入します。

1つ目の引数[文字列]は置換元データのセル、2つ目の引数[検索文字列]は元データ中の置換される文字列、3つ目の引数[置換文字列]は置き換える文字列です。

  =SUBSTITUTE(A2,"'","")

上の式は、セルA2の文字列でクォーテーション「'」を検索し「""」空白に置換する、という意味です。

SUBSTITUTE関数

数式をデータ分コピーします。

さて、このSUBSTITUTE関数は、答えが文字列となります。
データを数値化して求めたい場合は、文字列を数値化するVALUE関数(関数の分類: 文字列操作)を使用します。

  =VALUE(SUBSTITUTE(A2,"'",""))

先のSUBSTITUTE関数をまるごとVALUE関数にネストすることで、SUBSTITUTE関数の結果を数値化する、という意味になります。

その他、[形式を選択して貼り付け]によって、シングル クォーテーションをはずすこともできます。実はこちらの方が簡単に、複数列でも一気に処理できます。
方法は、ヘルプ「先頭にあるシングル クォーテーション、アポストロフィー(')を取るには(形式を選択して貼り付け編)」をご参照ください。

ヘルプmemo

<SUBSTITUTE関数 (文字列操作関数)>

文字列中の指定した文字列を新しい文字列に置換して返します。
 数式: =SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)
 引数:
   文字列 …置換の対象である文字列、またはセル参照で指定
   検索文字列 …指定した文字列の中での、置換対象となる文字列を指定
   置換文字列 …置換する文字列
   置換対象 …省略または、整数で指定[省略可]
 ヒント:
   引数[置換対象]を省略すると、検索された文字列すべてを置き換えます。
   数値nを指定すると、n番目に検索された文字列のみ置換されます。

<VALUE関数 (文字列操作関数)>

文字列として入力されている数字を数値に置換して返します。
 数式: =VALUE(文字列)
 引数:
   文字列 …文字列、またはセル参照で指定

twitter hatena line pocket

関連ヘルプ

7桁で入力されている郵便番号のデータにハイフォンを挿入したい 《REPLACE》
左から(右から・途中から)決まった文字数の文字列を取り出す 《LEFT・RIGHT・MID》
余分なスペースを削除してスペースを全角または半角に揃える 《SUBSTITUTE・TRIM》
全角の英数字を半角にするには、またはその逆 《ASC・JIS》
数字文字列を数値にする(関数編) 《VALUE》
別々のセルに入っているデータを結合して(つなげて)表示する 《アンパサンド・CONCAT (CONCATENATE) 》
2つの日付の期間が何年かを求める 《DATEDIF》
セルに入力されている数値を時刻の表示にする 《TIME》
時刻の「8:30」を「8.5」と数値に変換する
循環参照を解決するために
条件を満たすセルの個数を数える 《COUNTIF》
フィルターで抽出されたデータの件数、合計をセルに表示したい 《SUBTOTAL》
検索条件に一致するデータの合計を求める 《SUMIF》
負の小数値を合計したときに計算結果に誤差が生じる
カウントする関数が何をカウントするか 《COUNT・COUNTA・COUNTBLANK》
=のあとに+が表示された「=+」から始まる数式の意味とは
数式で参照しているセルを変更しても、結果が更新されない
セルに計算結果ではなく数式が表示されている
10進数の数値を2進数と16進数に変換するには 《DEC2BIN・DEC2HEX》
月の日数、月末を求める 《EOMONTH》