TEXT 函數

投稿人: Chris "Smitty" Smith 吳又倫

您可使用 TEXT 函數,以格式代碼來套用格式,藉此變更數字顯示的方式。若您希望以更容易閱讀的格式顯示數字,或者是要合併數字與文字或符號,這個做法就相當實用。

附註: TEXT 函數會將數字轉換成文字,但日後計算時可能難以做為參照。建議您在某個儲存格中保留原始值,然後在另外一個儲存格中使用 TEXT 函數。之後,若您想要建立其他公式,請一律參照原始值,而非 TEXT 函數的結果。

語法

TEXT(value, format_text)

TEXT 函數語法具有下列引數:

引數名稱

描述

value

您想要轉換成文字的數值。

format_text

文字字串,其定義您要套用到提供值的格式。

概觀

以其最簡單的形式而言,TEXT 函數表示:

  • =TEXT(您要設定格式的值, "您要套用的格式代碼")

下列是常見範例,您可直接複製到 Excel 中來自行實驗。請注意,格式代碼需置於雙引號內。

公式

描述

=TEXT(1234.567,"$#,##0.00")

貨幣代碼及千分位分隔符號與 2 位數小數位數,例如「$1,234.57」。請注意,Excel 會將值四捨五入至 2 位數小數位數。

=TEXT(TODAY(),"MM/DD/YY")

以 MM/DD/YY 格式顯示今天的日期,例如「03/14/12」

=TEXT(TODAY(),"DDDD")

星期幾,例如「星期一」

=TEXT(NOW(),"H:MM AM/PM")

目前的時間,例如「1:29 PM」

=TEXT(0.285,"0.0%")

百分比,例如「28.5%」

=TEXT(4.34 ,"# ?/?")

分數,例如「4 1/3」

=TRIM(TEXT(0.34,"# ?/?"))

分數,例如「1/3」。請注意,這會使 TRIM 函數移除前置空格,並使用十進位值。

=TEXT(12200000,"0.00E+00")

科學記號標記法,例如「1.22E+07」

=TEXT(1234567898,"[<=9999999]###-####;(###) ###-####")

特殊 (電話號碼),例如「(123) 456-7898」

=TEXT(1234,"0000000")

新增前置字元零 (0),例如「0001234」

=TEXT(123456,"##0° 00' 00''")

自訂 - 經緯度

附註: 雖然您可以使用 TEXT 函數來變更格式設定,但這並非唯一的做法。您可不透過公式來變更格式,方法是按下 CTRL+1 (若是 Mac,則請按 MAC Command 按鈕圖示的影像 +1),接著從 [儲存格格式] > [數值] 對話方塊中挑選所需格式即可。

下載我們的範例

您可下載範例活頁簿,取得本文所列的全部 TEXT 函數範例,以及部分額外範例。您可以直接沿用,也可以建立自己的 TEXT 函數格式代碼。

下載 Excel TEXT 函數範例

其他可用的格式代碼

您可以使用 [儲存格格式] 對話方塊找出其他可用的格式代碼:

  1. 按下 Ctrl+1 (若是 Mac,則請按 MAC Command 按鈕圖示的影像 +1) 以顯示 [儲存格格式] 對話方塊。

  2. 從 [數值] 索引標籤中選取所需的值。

  3. 選取 [自訂] 選項。

  4. 您所需的格式代碼會顯示在 [類型] 方塊中。在此情況下,請從 [類型] 方塊中選取分號 (;) 與 @ 符號以外的選項。以下範例中,我們可以僅選取並複製 mm/dd/yy。

  5. 按下 Ctrl+C 以複製格式代碼,然後按下 [取消] 來關閉 [儲存格格式] 對話方塊。

  6. 現在您只需按下 Ctrl+V,將格式代碼貼入 TEXT 公式中,像是 =TEXT(B2,"mm/dd/yy")。請確認您將格式代碼貼在雙引號之間 ("格式代碼"),否則 Excel 會回應錯誤訊息。

使用 [格式] > [儲存格] > [數值] > [自訂] 對話方塊讓 Excel 為您建立格式字串的範例。

依類別分類的格式代碼

以下範例說明您可以如何透過 [儲存格格式] 對話方塊為值套用不同的數字格式,並使用 [自訂] 選項將這些格式代碼複製到 TEXT 函數。

為什麼 Excel 會刪除前置字元 0?

Excel 的設定是尋找儲存格中正在輸入的數字,而非看起來像文字的數字,如部分為數字或 SKU 的數字。若要保留前置字元 0,請先將輸入範圍的格式設為 [文字],然後再貼上或輸入值。選取欄或您要輸入值的範圍,然後按下 CTRL+1 以顯示 [格式] > [儲存格] 對話方塊,並在 [數值] 索引標籤上選取 [文字]。如此一來,Excel 就會保留您的前置字元 0。

如果您已輸入資料,而 Excel 移除了前置字元 0,則可以使用 TEXT 函數重新加回。您可以參照頂端儲存格中的值並使用 =TEXT(value,"00000"),其中數字 0 的數目代表您要的字元總數,然後複製並貼到所需範圍。

使用 TEXT 格式化前置字元 0 的範例。=TEXT(A2,"00000")

如果出於某些原因,您需要將文字值轉換回數字,您可以乘以 1,像是 =D4*1,或是使用雙連字號運算子,像是 =--D4

如果格式包含以數字符號 (#) 或數字 0 括起來的逗號 (,),Excel 就會分隔千位數。例如,假設格式字串為 "#,###",Excel 就會將數值 12200000 顯示為 12,200,000。

在數字預留位置之後的逗號表示以 1,000 為計的數字。舉例來說,如果格式字串為 "#,###.0,",Excel 就會將數字 12200000 顯示為 12,200.0。

使用千位分隔符號的 TEXT 函數範例

附註: 

  • 千分位分隔符號取決於您的地區設定。在美國地區是逗號,但在其他地區可能是小數點 (.)。

  • 千分位分隔符號適用於數字、貨幣與會計格式。

以下是標準數字的範例 (僅限千分位分隔符號與小數)、貨幣與會計格式。貨幣格式可讓您選擇插入貨幣符號,並與您的值貼齊,而會計格式則是會將數字符號靠儲存格左側對齊,並將值靠右對齊。請注意,貨幣與會計格式代碼之間的差異如下,其中會計格式會使用星號 (*) 來區隔符號與值。

TEXT 函數搭配數字、貨幣和會計格式的範例

若要尋找貨幣符號的格式代碼,請先按 Ctrl+1 (若是 Mac,則請按 MAC Command 按鈕圖示的影像 +1),選取所需的格式,然後從 [符號] 下拉式選單中選擇符號:

[設定儲存格格式] 對話方塊的貨幣符號選項

接著,從左側的 [類型] 區段中按一下 [自訂],然後複製包含貨幣符號在內的格式代碼。

TEXT 函數 - 自訂貨幣和符號

附註: TEXT 函數不支援色彩格式,因此如果您從 [儲存格格式] 對話方塊中複製包含色彩的數字格式代碼,例如:$#,##0.00_);[Red]($#,##0.00),TEXT 函數會接受此格式代碼,但不會顯示色彩。

您可以使用 "M" (月)、"D" (日)、"Y" (年) 的組合來更改日期顯示方式。

月、日和年的格式代碼

TEXT 函數中的格式代碼不需區分大小寫,因此您可以使用 "M" 或 "m"、"D" 或 "d"、"Y" 或 "y"。

Excel 最有價值專家 Mynda Treacy

Mynda 的建議...

如果您和來自不同國家的使用者共用 Excel 檔案和報表,那麼您可能會想要在給他們的報表中使用他們的語言。Excel 最有價值專家,Mynda Treacy不同語言中所顯示的 Excel 日期 (英文) 一文中提出了絕佳的解決方法。其中也包括您可以下載的活頁簿範例。

您可以使用 "H" (小時)、"M" (分) 或 "S" (秒) 的組合,加上 "AM/PM" (12 小時制) 來更改時間顯示方式。

時、分和秒的時間格式

如果您未指明 "AM/PM" 或 "A/P",則時間會根據 24 小時制顯示。

TEXT 函數中的格式代碼不需區分大小寫,因此您可以使用 "H" 或 "h"、"M" 或 "m"、"S" 或 "s"、"AM/PM" 或 "am/pm"。

您能以百分比 (%) 格式來更改小數數值的顯示方式。

百分比的格式代碼

您能以分數 (?/?) 格式來更改小數數值的顯示方式。

分數的格式代碼

科學記號標記法是以 1 到 10 的十進制數字,並搭配 10 的次方相乘來顯示數字的方式。常用於縮寫巨大數字的顯示方式。

科學記號的格式代碼

Excel 提供 4 種特殊格式:

  • 郵遞區號 - "00000"

  • 郵遞區號 +4 - "00000-0000"

  • 電話號碼 - "[<=9999999]###-####;(###) ###-####"

  • 社會安全號碼 - "000-00-0000"

TEXT 函數的特殊格式

特殊格式會隨地區而有所不同,但要是沒有適用於您所在地區的特殊格式,或是上述格式並不符合您的需求,則您可以透過 [儲存格格式] > [自訂] 對話方塊來建立自己的格式。

常見案例

TEXT 函數很少單獨使用,通常會搭配其他項目使用。假設您想將文字與數字合併成「報表列印日期:03/14/12」或「每週收入:$66,348.72」。您可以手動在 Excel 中輸入這些資訊,但這麼做就違反了讓 Excel 為您處理事務的目的。遺憾的是,當您合併文字和設定數字格式 (如日期、時間、貨幣等) 時,Excel 無法得知您要如何顯示這些內容,因此會省略數字格式。這就是 TEXT 函數如此重要的原因,因為它能使用格式代碼強制 Excel 將值設定成您想要的格式,例如日期格式 "MM/DD/YY"

下列範例將說明如果您嘗試合併文字與數字,而不使用 TEXT 函數會產生什麼結果。在此情況下,我們使用 & 符號將文字字串、空格 (" ") 和值串連起來:=A2&" "&B2

不使用 TEXT 函數加入文字的範例

如您所見,Excel 會將儲存格 B2 中的格式設定移除。在下個範例中,您將會看到 TEXT 函數可如何協助您套用所需的格式。

使用 TEXT 函數加入文字的範例

我們已更新的公式為︰

  • 儲存格 C2:=A2&" "&TEXT(B2,"mm/dd/yy") - 日期格式

常見問題集

很抱歉,目前無法使用 TEXT 函數執行這項操作。請使用 Visual Basic for Applications (VBA) 程式碼執行。請參閱以下連結提供的方法︰如何在 Excel 中將數值轉換成英文字詞

是,您可以使用 UPPERLOWERPROPER 函數來達成此目的。例如,=UPPER("hello") 會傳回 "HELLO"。

是,但需要採取幾個步驟。首先,選取您要設定的儲存格,然後使用 Ctrl+1 以顯示 [格式] > [儲存格] 對話方塊,移至 [對齊] > [文字控制],然後選取 [自動換行] 選項。接下來,調整已完成的 TEXT 函數,然後在要換行的位置加入 ASCII 函數 CHAR(10)。視最終結果的對齊方式而定,您可能需要調整欄的寬度。

搭配 CHAR(10) 使用 TEXT 插入分行符號的範例。="Today is: "&CHAR(10))&TEXT(TODAY(),"MM/DD/YY")

在此情況下,我們使用了:="Today is: "&CHAR(10)&TEXT(TODAY(),"mm/dd/yy")

這稱為科學記號標記法,Excel 會自動轉換超過 12 位數 (若儲存格的格式設定為 [一般]) 以及 15 位數 (若儲存格的格式設定為 [數值]) 的數字。如果您需要輸入較長的數值字串,但不想要進行轉換,則請將問題中的儲存格格式設定為 [文字],再將值輸入或貼入 Excel。

Excel 最有價值專家 Mynda Treacy

Mynda 的建議...

如果您和來自不同國家的使用者共用 Excel 檔案和報表,那麼您可能會想要在給他們的報表中使用他們的語言。Excel 最有價值專家,Mynda Treacy不同語言中所顯示的 Excel 日期 (英文) 一文中提出了絕佳的解決方法。其中也包括您可以下載的活頁簿範例。

請參閱

建立或刪除自訂數字格式

將文字轉換成數值

所有 Excel 函數 (依類別)

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×