「Excelに入力されている表示形式で値を抜き出したい」
「文字列を日付に変えたいんだけどどうすればいいかわからない」
このような対応や疑問にぶつかったときに便利なのがTEXT関数です。
TEXT関数とは、表示形式を任意に指定して値を抜き出すことができ、使いこなせば簡単にExcel上の表示形式を変えることができるようになります。
ただ、指定方法がたくさんあり、やや複雑な関数でもあります。
そこで今回の記事では、TEXT関数の基本的な使い方からよく使う表示形式の指定方法まで解説します。
表示形式の処理に悩んでいたり、日付や曜日を文字列で打ち込んでいたりした方は、本記事を読むことでTEXT関数を使いこなせるようになります。
ぜひ、参考にしてくださいね。
目次
TEXT関数について
TEXT関数とは数値に指定した書式を設定し、文字列に変換した結果を返すことができる関数です。
たとえば、Excelで「43466」という数値に「”yyyy/mm/dd”」という書式を指定すると、「2019/01/01」という日付が表示されます。
この他にも日付のデータから曜日を表示させる、和暦に変えるなど、TEXT関数は様々なことができます。TEXT関数を使用すると、表示形式コードを使用して数値に書式設定を適用することで、数値の表示方法を変更することができます。
これは、数値をより読みやすい形式で表示する場合や、数値を文字列または記号と組み合わせる場合に便利です。
TEXT関数は、基本的に数値を文字列に変換する関数です。そのため、後の計算で参照するのが難しくなるおそれも。
ですから単一のセルに元の値を保持し、別のセルで TEXT 関数を使用することを推奨します。他の数式を作成する必要がある場合は必ず、TEXT関数の結果ではなく、元の値を参照するようにしてください。
TEXT関数の書き方
EXT関数の構文は次の通りです。
| TEXT（値,表示形式） |
TEXT関数の構成要素には次の引数があります。
値：テキストに変換する数値。
表示形式：指定された値に適用する書式を定義するテキスト文字列。
最もシンプルなTEXT関数は次のようになります。
| TEXT(書式設定する値, “適用する表示形式コード”) |
TEXT関数の使い方
ここからは実際にTEXT関数を使った例をみていきましょう。
TEXT関数を使って日付と曜日を表示する方法
Excelで入力した日付から曜日を自動的に入力するには、A1セルに日付を入力
B1セルに曜日を表示したい場合はB1セルに以下のように入力します。
これで自動的にB1セルに日付に対応する曜日が表示されました。
曜日を入力したいセルを選択し、日付の入ったセルを参照するTEXT関数「=TEXT(A1,”aaaa”)」を入力します。
どのような形式で曜日を表示するかは次の表示形式コードで指定しましょう。
| 表示形式コード | 曜日の表示形式 |
| aaa | 月、火、水、木、金、土、日 |
| aaaa | 月曜日、火曜日、水曜日、木曜日、金曜日、土曜日、日曜日 |
| ddd | Mon、Tue、Wed、Thu、Fri、Sat、Sun |
| dddd | Monday、Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday |
上の表の「”aaaa”」の部分（表示形式コード）を変えることで、曜日の表示形式が変更できます。なお、オプションを「()」で囲うと「(月)」や「(Mon)」、「｛｝」で囲うと「{月}」、「{Mon}」といった形式でも表示可能になります。
また、TEXT関数を使うと曜日を別のセルに入力することになります。
もし、日付と同じセルに曜日も入力したい場合は、「セルの書式設定」で変更しましょう。
方法は3つあります。
【1.1つのセルに日付と曜日を表示させる】
日付と同じセルに曜日を表示したい場合は、セルの書式設定を使います。このとき、日付を入力したセルを選択し、右クリックメニューから［セルの書式設定］をクリックします。
【2.1つのセルに日付と曜日を表示させる】
［セルの書式設定］ダイアログが表示されたら、［表示形式］タブを開き、「分類」欄で［ユーザー定義］を選択し、「種類」を「yyyy/m/d aaa」に編集します。ここを設定することで、表示形式を和暦などに変更することも可能になります。
【3.1つのセルに日付と曜日を表示させる】
日付のセルに曜日が合わせて表示されたのを確認しましょう。
もし曜日を英語表記にしたい場合は、「yyyy/m/d」の「d」の後ろに半角スペースを入れて「ddd」（「yyyy/m/d ddd）もしくは「dddd」（「yyyy/m/d dddd」）とすればOKです。
「ddd」とすると「Mon」や「Thu」のように短縮表示に、「dddd」とすると「Monday」「Thursday」のようにフル表示されます。
＜短縮表示＞
＜フル表示＞
TEXT関数と他の関数を組み合わせて使う方法
実のところTEXT 関数は、単独で使用されることはほとんどありません。
一般的に他の関数と組み合わせて使用されます。
たとえば、“Report Printed on: 03/14/12” や “Weekly Revenue: $66,348.72” のように、テキストと数値を組み合わせるとします。
これ自体はExcelに手入力できますが、Excel に自動的に入力させるという目的が無意味になるばかりでなく、膨大なデータ量を扱う作業においては現実的ではありません。
また、日付、時刻、通貨など、文字列と書式設定された数値を組み合わせると、Excel はその表示方法を認識することができないので、数値の書式設定は削除されてしまいます。
このときに活躍するのがTEXT関数。
TEXT関数を使用すると、日付の表示形式を表す “MM/DD/YY” などの表示形式コードを使用して、Excel で強制的に目的の書式を値に設定できるようになります。
Microsoftサポートの例でみていきましょう。
下図はTEXT関数を使用しないで文字列と数値を結合した場合にどうなるかを示しています。 この場合、アンパサンド (&) を使用して =A2&” “&B2 を指定することで、テキスト文字列、スペース (” “)、および値を連結しています。
このようにExcelでは、セルB2の日付の書式設定が削除されます。
次の図では、TEXT関数を使用して目的の書式を適用する方法を示しています。
更新された数式は
| C2=A2&” “&TEXT(B2,”mm/dd/yy”) |
と入力されています。
このようにTEXT関数は、数値や日付を文字列に変換するだけでなく、他の文字列とつなげて利用することができます。
セルの値が変更される度、一度にデータの更新が行えるようにしておくと入力や更新の手間が省け、業務効率化にもつながります。
出典：Microsoftサポート｜TEXT関数｜一般的なシナリオ
TEXT関数でよく使われる表示形式
ここでは、TEXT関数で適用する表示形式コードをいくつかご紹介します。
表示形式コードとはセルに入力した数値データを、値を変えずに見た目のみを変えることができる形式のことです。
実際にお使いのExcelに直接コピーして試してみることができますが、 表示形式コードを引用符で囲む必要があるので注意してくださいね。
| 数式の書き方 | 内容の説明 |
| =TEXT(TODAY(),”MM/DD/YY”) | 月/日/年の形式の今日の日付 （例: 03/14/12） |
| =TEXT(TODAY(),”DDDD”) | 今日の曜日 （例: Monday） |
| =TEXT(NOW(),”H:MM AM/PM”) | 現在の時刻 （例: 1:29 PM） |
| =TEXT(1234.567,”$#,##0.00″) | 桁区切り記号と小数点以下2桁を設定した通貨 （例 $1,234.57）※値は小数点以下2桁に四捨五入されます。 |
| =TEXT(0.285,”0.0%”) | パーセンテージ （例: 28.5%） |
| =TEXT(12200000,”0.00E+00″) | 指数表記 （例: 1.22E+07） |
| =TEXT(4.34 ,”# ?/?”) | 分数 （例: 4 1/3） |
| =TRIM(TEXT(0.34,”# ?/?”)) | 分数 （例: 1/3）。この場合はTRIM関数を使用して小数点の先頭のスペースと10進数を削除することに注意してください。 |
| =TEXT(1234567898,”[<=9999999]###-####;(###) ###-####”) | その他 （電話番号）（例: (123) 456-7898） |
| =TEXT(1234,”0000000″) | 先頭のゼロ （0）を追加 （例: 0001234） |
| =TEXT(123456,”##0° 00′ 00””) | ユーザー設定 （緯度/経度） |
出典：Microsoftサポート｜TEXT関数｜表示形式コード
また、表示形式の設定は、[セルの書式設定]ダイアログボックスからも操作可能です。
操作方法は次のとおりです。
- 設定するセルを選択
- [ホーム]タブ→[数値]
- 右下の↘ボタンをクリック
- [セルの書式設定]ダイアログボックスが表示
- [表示形式]タブをクリックし、[分類]項目の12種類の表示形式から入力データの用途に合わせて目的の表示形式を選択
目的や作業時間にあわせて使いやすい方を選んでくださいね。
あわせて読みたい｜TEXT関数以外で数値を文字列として設定する方法
最後に、TEXT関数を使わずに既存の数値を文字列として設定する方法をお伝えします。
Excelで特定の種類の数値を文字列として処理する際には、数値の代わりに文字列形式を使用することができます。
たとえば、クレジットカード番号のように16 桁以上を含むその他の番号コードを使用している場合は、テキスト形式を使用する必要があります。
なぜなら、Excelで有効な最大桁数が15 桁であり15桁を超えた数値は切り捨てられるためです。数値の表示形式が文字列になっている場合、数値はセルで右揃えではなく左揃えで表示されるためすぐに気が付くでしょう。対応方法は次の通りです。
テキストとして書式設定する範囲を含むセルを選択します。
[ホーム ] タブの [数値] グループで [数値の書式] ボックスの横にある矢印をクリックし、[テキスト] を押します。この時に、[テキスト] オプションが表示しない場合はスクロールバーを使用して一覧の末尾まで移動させます。| 注意事項1.文字列として保存する数値で小数点以下の桁を使用するには、数値を入力するときに小数点を含める必要があります。 2.ゼロで始まる数値 (製品コードなど) を入力すると、既定ではゼロが削除されます。削除されないようにするには、先行ゼロが保持されるユーザー設定の数値形式を作成します。たとえば、10桁の製品コードをワークシートに入力または貼り付ける場合、01784367996 は1784367996に変換されます。コード「0000000000」で構成されるユーザー設定の数値形式を作成して、先行ゼロを含む 10 桁の製品コード全体が表示されるためです。 3.場合によっては、数値が文字列形式としてセルに保存され、後で計算結果エラーが生じたり、並べ替えができなくなったりすることがあります。これは、データベースやその他のデータソースから数値をインポートまたはコピーしたときなどに発生するエラーです。その際の対処法としては、文字列として保存されている数値を数値形式に変換する必要があります。 出典：Microsoftサポート｜数値を文字列として設定する |
まとめ
いかがでしたでしょうか。
今回の記事では、TEXT関数の基本的な使い方や表示形式についてご説明してきました。
Excelでの作業は計算ばかりでなく、データ入力や日付から曜日を必要とする表作成などもありますよね。TEXT関数を使いこなせば、日付などの表示がスムーズになるだけでなく、他の関数と一緒に使うことで自由に文字と組み合わせることができます。
Excelは手作業でできることもありますが、人為的ミスが起こりやすく、非効率です。
特にビッグデータのように膨大な量を扱う作業の場合は、関数やマクロで自動化することが推奨されています。
ぜひ本記事を参考にTEXT関数をマスターし、Excelスキルを強化していってくださいね。
