ExcelのTEXT関数は、数値に表示形式を適用して文字列に変換する関数です。表示形式は書式記号で表します。
ところで、セルの書式設定で表示形式を変えることができるのに、わざわざTEXT関数を使って表示形式を変えるだけであれば全く意味がありません。TEXT関数は、数式の一部として利用したり、別の関数の中に入れて入れ子にしたりします。
今回は、TEXT関数の使い方、文字列への変換、数値への変換、日付や時刻との関係などについて出題します。
目次
- ０．表示形式の書式記号まとめ（復習）
- １．TEXT関数の基本
- ２．換算や文字列削除はできない
- ３．TEXT関数の文字列を数値に変換する
- ４．カンマ桁区切り、指数表示
- ５．スラッシュで1文字ずつ区切る
- ６．パーセントと数値化
- ７．セミコロン
- ８．ROUND関数とTEXT関数の違い
- ９．文字列と連結するときの注意点
- 10．IF関数で場合分けをする
- 11．補足：色や太字の指定は不可
０．表示形式の書式記号まとめ（復習）
書式記号についてはこちらの記事をご覧ください。
１．TEXT関数の基本
（１）表示形式を適用する
問題
A列に7桁以内の整数を入力した。B列に、TEXT関数を用いて「000-0000」の表示形式の文字列に変換しなさい。
解説
TEXT関数は、数値に表示形式を適用して、その結果を文字列として返します。表示形式を指定するにはユーザー定義表示形式の書式記号を使います（上記記事参照）。ダブルクォーテーションで囲みます。
- =TEXT(数値,"表示形式の書式記号")
「=text(a1,"000-0000")」と入力します。「0」は数字1桁を表し、桁数が足りない場合は0で埋めるという意味です。3桁目と4桁目の間にハイフンを入れます。
TEXT関数挿入の画面を用いてもかまいません。
7桁の郵便番号の形式になります。
（２）セルの書式設定とTEXT関数の違い
問題
A列の表示形式を「000-0000」にしなさい。また、セルの書式設定によって表示形式を設定するのと、TEXT関数を表示形式を適用するのでは何が違うか述べなさい。
解説
A列を選択します。
セルの書式設定、表示形式のユーザー定義で「000-0000」にします。
これで、A列の表示形式が「000-0000」となり、7桁の郵便番号の形式になります。
A列とB列は全く同じに見えますが、まったく違うものです。A列はセルの値を変えることなく、表示形式を変えています。数式バーには元の数値が表示されています。
表示形式を「標準」にすると元の数値に戻ります。
B列は「標準」でこの状態なので、他の表示形式に変えても表示は変わりません。TEXT関数を使うとその計算結果は「文字列」なので表示形式は適用されません。
A列には数値が保存されています。10倍すると、10倍した値が出ます。
一方、B列を10倍するとエラーです。TEXT関数は文字列なので掛け算することができません。
後ろに「円」を連結します（参考：【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結）。A列のほうは元の数値に「円」が付きます。掛け算にしても、文字列連結にしてもその計算で利用される値は元の数値であり、表示形式は無関係です。
一方、B列に「円」を連結すると、郵便番号の後に円がつきます。TEXT関数は元の数値の情報を持たないので、表示形式で変換された後の文字列が演算に使われます。
２．換算や文字列削除はできない
問題
TEXT関数を用いてmをcmに変換することは可能か。また、TEXT関数を用いて「円」を削除することは可能か。
解説
表示形式は、セルの値を変えずにセルの表示を変えるものであって、原則として元のデータの大きさを変えるものではありません。したがって、TEXT関数のみで元の値を変えることはできません。
mをcmに換算するには100倍すればよく、cmをつけたければ「&"cm"」とすればよいです。このように、桁数を増やす、減らす、換算するといった掛け算・割り算でできる計算をTEXT関数でやろうとしてはいけません。
ただし、換算したものに対して、TEXT関数を用いることは可能です。例えば、100倍してから「0m00cm」の表示形式にしたい場合は、「=TEXT(A1*100,"0!m00c!m")」とすればよいです（！については後述）。
また、TEXT関数で指定できるセルは「数値」であり、文字列を指定することはできません。したがって、「円」を削除することはできません。この場合はLEFT関数で「=LEFT(C1,LEN(C1)-1)」とするべきです（参考：Excelで特定の文字を検索して文字列の一部を抽出する【LEN、FIND、LEFT、RIGHT、MID】）。
この他、全角半角の変換（JIS関数、ASC関数）、大文字小文字の変換（UPPER関数、LOWER関数）、セル内改行の追加（CHAR(10)の連結）などの文字列操作は、それぞれ文字列操作関数や演算子を用いるべきであり、TEXT関数を使ってはいけません。
３．TEXT関数の文字列を数値に変換する
問題
（１）数式「=TEXT(123,"000")」で得られる123と、数値の123は同じか。
（２）「=TEXT(123,"000")+TEXT(123,"000")」を計算することは可能か。
解説
123と入力します。
「=TEXT(A1,"000")」と入力します。123を3桁の表示「000」にするだけなので123のままです。見た目は同じですが左揃えになります。
イコールで比較するとFALSEになります。TEXT関数で得られる答えは文字列であり、数値の123とは異なるものです。
1を掛けます。1を掛けると通常はエラーになりますが、数値に変換できるものは数値化されます。
- =TEXT(A1,"000")*1
TEXT関数の文字列を数値化するには次のような方法があります。
- １をかける TEXT*1
- ０をたす TEXT+0
「=TEXT(123,"000")+TEXT(123,"000")」と入力します。246になります。
通常は、文字列同士の演算をすることはできませんが、数値化できる文字列は、その直前か直後に四則演算（足し算引き算掛け算割り算）があれば数値とみなされるため、計算をすることができます。
＊補足＊
数値と文字列の違い、変換方法の基本についてはこちらの記事をご覧ください。
４．カンマ桁区切り、指数表示
問題
セルA1に12345と入力した。TEXT関数を用いて「残り12,345円となります。」「1.23E+04」という形式にしなさい。
解説
カンマ桁区切りにします。
これに「円」を連結しても「12,345円」にはなりません。参照元のセルに表示形式を設定しても、それを参照した数式にその表示形式は反映されません。
カンマ区切りの表示形式の記号は「#,##0」です。「="残り"&TEXT(A1,"#,##0")&"円となります。"」とします。これで「残り12,345円となります。」となります。
なお、日本語の部分をTEXT関数の中に入れて「=TEXT(A1,"残り#,##0円となります!。")」としてもかまいません。この場合は＆は不要です。
また、本来なら「"""残り""#,##0""円となります。"""」とダブルクォーテーション2つで囲むべきですが、「"残り#,##0円となります!。"」で良いです。
小数点第2位までの指数表示の記号は「0.00E+00」です（参考：【Excel】なぜ指数の表示形式「Ｅ」が表示されるのか、「Ｅ」の設定と解除の方法）。「=TEXT(A1,"0.00E+00")」とします。これで「1.23E+04」となります。
５．スラッシュで1文字ずつ区切る
問題
セルA1に12345と入力した。TEXT関数を用いて「1/2/3/4/5」という形式にしなさい。
解説
「=TEXT(A1,"0/0/0/0/0")」と入力します。これはエラーです。
スラッシュは分数の表示形式の記号として使われるものなので、先頭に半角の！をつけるかダブルクォーテーションで囲むことが必要です。「=TEXT(A1,"0!/0!/0!/0!/0")」とします。
別解
REPT関数とLEN関数を使う場合は、「=TEXT(A1,REPT("0!/",LEN(A1)-1)&"0")」となります。これで文字数に関係なくスラッシュで区切ることができます。
一般に「X」で区切る場合は、「=TEXT(文字列,REPT("0!X",LEN(文字列)-1)&"0")」とします。なお、！が不要な場合もあります。
６．パーセントと数値化
問題
セルA1に0.56789と入力した。TEXT関数を用いて、パーセンテージ小数第1位までの表示にしなさい。また、これを数値化するといくつになるか。
解説
「=TEXT(A1,"0.0%")」と入力します。56.8%となります。
1を掛けます。「0.568」となります。元の値は0.56789ですが、TEXT関数によって0.568となります（四捨五入になる）。このようにTEXT関数を用いた後で数値化すると、元の値と異なることがあります。
７．セミコロン
（１）0以上とマイナス
問題
A列のうちプラスまたはゼロの場合は「A」、マイナスの場合は「B」と表示しなさい。
解説
「=TEXT(A1,"!A;!B")」と入力します。プラスまたはゼロの場合は「A」、マイナスの場合は「B」になります。
＊補足＊AやBのように半角1文字の文字、記号、数字の場合、！を付けておいたほうが良いです。
表示形式書式記号の中にセミコロンが１つある場合、セミコロンの左がプラスまたはゼロ、セミコロンの右がマイナスを表します（参考：【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ）。
（２）プラスとマイナスとゼロ
問題
A列のうちプラスの数の場合は先頭に＋を付け、マイナスの数の場合はマイナスを▲にし、ゼロの場合は「なし」にしなさい。
解説
「=TEXT(A1,"+0;▲0;なし")」と入力します。プラスの場合は＋、マイナスの場合は▲がつき、ゼロの場合は「なし」になります。
表示形式書式記号の中にセミコロンが２つある場合、最初がプラス、2番目がマイナス、3番目がゼロを表します。
８．ROUND関数とTEXT関数の違い
（１）ROUNDは数値、TEXTは文字列
問題
セルA1に0.56789と入力した。TEXT関数を用いて小数第2位までの表示にしなさい。また、ROUND関数を用いた場合との違いを述べなさい。
解説
「=TEXT(A1,"0.00")」と入力します。TEXT関数を用いた場合、表示形式のルールにより端数は四捨五入になります。0.57となり、左揃えになります。
ところで、ROUND関数も四捨五入をする関数です（参考：【Excel】ROUND関数の桁数は「小数点の位置」を０として考えると分かりやすい）。桁数は2です。0.57となり、右揃えになります。
このようにTEXTは文字列を返し、ROUNDは数値を返します。もちろん1をかけることによってTEXTも数値にすることはできますが、単に四捨五入をするだけであれば、ROUNDを使うべきです。
（２）四捨五入をして０も表示する
問題
セルA1に0.5697と入力した。TEXT関数を用いて小数第3位までの表示にしなさい。また、ROUND関数を用いた場合との違いを述べなさい。
解説
「=TEXT(A1,"0.000")」と入力します。0.570となります。文字列なので最後の0も表示されます。
ROUND関数の場合、桁数は3です。数値の場合、最後の0は表示されないので桁数＝３を指定しても、3桁表示されないことがあります。
「小数点以下の表示桁数を増やす」ボタンで０を表示します。
９．文字列と連結するときの注意点
（１）パーセンテージの連結
問題
セルA1に56.97％と入力した。これを用いて「この商品の占める割合は56.97％であった。」と表示しなさい。
解説
文字列連結の計算式を入力したとします（参考：【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結）。文字列連結をすると、56.97％の元の数値である「0.5697」になってしまいます。
- ="この商品の占める割合は"&A1&"であった。"
パーセンテージの表示形式を保ちながら文字列連結をするには、TEXT関数を使います。
- ="この商品の占める割合は"&TEXT(A1,"0.00%")&"であった。"
（２）四捨五入をし、文字列を連結して０も表示する
問題
セルA1に56.97％と入力した。小数第2位を四捨五入して、「この商品の占める割合は57.0％であった。」と表示しなさい。
解説
TEXT関数で連結します。
- ="この商品の占める割合は"&TEXT(A1,"0.0%")&"であった。"
ちなみに、ROUND関数を用いると最後の0は表示されません。
- ="この商品の占める割合は"&ROUND(A1*100,1)&"%であった。"
10．IF関数で場合分けをする
問題
TEXT関数を用いて、A列の数値が1未満の場合は小数第3位まで、1以上の場合は小数第2位までの表示にしなさい。
解説
元の値の大きさによって表示形式を変える場合はIF関数を使います。
- =IF(A1<1,TEXT(A1,"0.000"),TEXT(A1,"0.00"))
別解
書式記号を変えるだけであれば、TEXT関数の中にIFを入れてもよいです。
- =TEXT(A1,IF(A1<1,"0.000","0.00"))
11．補足：色や太字の指定は不可
表示形式で[赤]のように色の指定をすることができますが、TEXT関数は文字列を返すだけなので、色の指定はできません。また、表示形式ではない書式（例：太字、フォントサイズなど）も不可です。TEXT関数ではなく、条件付き書式かマクロを使うべきでしょう。
解説は以上です。
