数値や日時の値を表示形式が設定された文字列に変換する(TEXT)

文字列と数値や日時の値を結合する場合、数値や日時に表示形式が設定されていても表示形式が設定されていない値と結合が行われます。表示形式が設定された状態で文字列と結合したい場合、先に数値や日時の値を表示形式が設定された文字列に変換したあとで文字列と結合します。ここでは Excel の関数である TEXT 関数を使って数値や日時の値を表示形式が設定された文字列に変換する方法について解説します。

(Last modified: 2023年07月10日)

表示形式が設定されたまま文字列と結合する

& 演算子を使ったり TEXTJOIN 関数を使うことで文字列と値を結合することができますが、値が数値の場合で表示形式が設定されている場合に値を結合すると表示形式が設定されていない値と結合が行われます。例えば 3450 という数値に表示形式を設定して 3,450 と表示していた場合、文字列と結合する場合は 3450 と結合されます。

また日付や時刻の値と結合する場合も注意が必要です。例えば 2016/10/07 や 16:35:02 などの日付や時刻の値は内部的に 42650 や 0.69099537 などのシリアル値と呼ばれる数値で保管されています。 Excel 上ではシリアル値に対して日付や時刻の表示形式を設定した上で表示がされていますが、文字列と結合する場合にはシリアル値が結合されます。

次のシートを見てください。

まず & 演算子を使って文字列と値を結合してみます。 D3 セルから D5 セルに次のような数式を入力しました。

数値は表示形式が設定されていない状態の数値と文字列が結合し、日付や時刻の値は内部的に保管されているシリアル値が文字列と結合しました。

数値や日時の値を表示形式が設定された状態で文字列と結合するには、まず TEXT 関数を使って対象の値を指定した表示形式を設定した文字列に変換し、その文字列を結合します。

例えば桁区切りの表示形式が設定された数値を、表示されているまま文字列と結合するには D3 セルに次のような数式を入力します。

Enter キーを押すと、数値が表示形式が設定された状態で文字列と結合されました。

次に日付の値を表示されているまま文字列と結合するには D4 セルに次のような数式を入力します。

Enter キーを押すと、日時の値が表示形式が設定された状態で文字列と結合されました。

最後に時刻の値を表示されているまま文字列と結合するには D5 セルに次のような数式を入力します。

Enter キーを押すと、時刻の値が表示形式が設定された状態で文字列と結合されました。

このように TEXT 関数を使用することで数値や日時の値を指定した表示形式が設定された文字列に変換することができますが、それぞれどのような表示形式を設定するか個別に設定する必要があります。

解説

結合する値に設定されている表示形式を取得して TEXT 関数の引数に指定できれば便利なのですが、 VBA を使用すればできるようですが関数だけでは表示形式を取得する方法がありません。( CELL 関数で定数という形で表示形式を取得できますが、定数から実際の表示形式に変換する方法が分かりません)。

なお対象のセルに現在設定されている表示形式を確認するには、対象のセルをクリックして選択したあとで Ctrl + 1 をクリックしてください。

「セルの書式設定」ダイアログが表示されます。「表示形式」タブの「分類」で「ユーザー定義」をクリックしてください。

「種類」に表示された値が現在設定されている表示形式です。

今回使用した関数の解説は下記を参照されてください。

-- --

Excel の関数である TEXT 関数を使って数値や日時の値を表示形式が設定された文字列に変換する方法について解説しました。