您是否曾經在 Excel 中導入或輸入包含前導零 (如00123或 1234 5678 9087 6543 的大型數字) 的數據?例如社會保險號、電話號碼、信用卡號、產品代碼、帳號或郵政編碼。Excel 將自動刪除前導零, 并將大量數字轉換為科學記數法 (如 1.23 e + 15), 以便允許公式和數學運算處理它們。本文介紹了如何保持數據的原始格式, Excel 將其視為文本。
導入文本數據時將數字轉換為文本
較新版本 Office 2010-2013
在導入數據時, 使用 Excel 的Get & 轉換 (Power Query)體驗將各列的格式設置為文本。在這種情況下, 我們將導入文本文件, 但對于從其他源 (如 XML、Web、JSON 等) 導入的數據, 數據轉換步驟是相同的。
-
單擊 “數據” 選項卡, 然后單擊 “獲取數據” 按鈕旁邊的 “文本/CSV “。如果看不到 “獲取數據” 按鈕, 請轉到 “新建查詢>從文本>”, 然后瀏覽到您的文本文件, 然后按 “導入”。
-
Excel 會將數據加載到預覽窗格中。在預覽窗格中按 “編輯” 以加載查詢編輯器。
-
如果需要將任何列轉換為文本, 請通過單擊列標題選擇要轉換的列, 然后轉到 “開始>轉換>”數據類型> 選擇文本。
提示:?您可以通過Ctrl + 單擊選擇多個列。
-
下一步, 單擊 “更改列類型” 對話框中的 “替換當前”, Excel 會將所選列轉換為文本。
-
完成后, 單擊 “關閉 & 加載”, Excel 會將查詢數據返回到工作表。
如果未來的數據發生變化, 您可以轉到 “數據>刷新”, Excel 將自動更新您的數據, 并為您應用轉換。
在 Excel 2010 和2013中, 有兩種方法可導入文本文件并將數字轉換為文本。建議的方法是使用 power query, 這可在下載 Power query 加載項時使用。如果無法下載 Power Query 加載項, 則可以使用 “文本導入向導”。在這種情況下, 我們將導入文本文件, 但對于從其他源 (如 XML、Web、JSON 等) 導入的數據, 數據轉換步驟是相同的。
-
單擊功能區中的 ” Power Query ” 選項卡, 然后從文本中選擇 “獲取外部數據>”。
-
Excel 會將數據加載到預覽窗格中。在預覽窗格中按 “編輯” 以加載查詢編輯器。
-
如果需要將任何列轉換為文本, 請通過單擊列標題選擇要轉換的列, 然后轉到 “開始>轉換>”數據類型> 選擇文本。
提示:?您可以通過Ctrl + 單擊選擇多個列。
-
下一步, 單擊 “更改列類型” 對話框中的 “替換當前”, Excel 會將所選列轉換為文本。
-
完成后, 單擊 “關閉 & 加載”, Excel 會將查詢數據返回到工作表。
如果未來的數據發生變化, 您可以轉到 “數據>刷新”, Excel 將自動更新您的數據, 并為您應用轉換。
使用自定義格式保留前導零
如果你想要在工作簿中解決此問題, 因為它不是由其他程序用作數據源, 因此你可以使用自定義或特殊格式保留前導零。這適用于包含少于16位的數字代碼。此外, 你可以用短劃線或其他標點符號設置號碼代碼的格式。例如, 若要使電話號碼更具可讀性, 可以在國際代碼、國家/地區代碼、區域代碼、前綴和最后幾個數字之間添加短劃線。
號碼代碼
示例
自定義數字格式
社交 債券
012345678
000-00-0000 012-34-5678
電話
0012345556789
00-0-000-000-0000 00-1-234-555-6789
郵政 區號
00123
00000 00123
步驟???
-
選擇要設置格式的單元格或單元格區域。
-
按Ctrl + 1加載 “設置單元格格式” 對話框。
-
選擇 “數字” 選項卡, 然后在 “類別” 列表中, 單擊 “自定義”, 然后在 “類型” 框中鍵入數字格式, 如000-00-0000表示社會保險號碼代碼, 或00000為五位數的郵政編碼。
提示:?還可以單擊“特殊”,然后選擇“郵政編碼”、“郵政編碼 + 4”、“電話號碼”或“社會保險號碼”。
有關自定義代碼的詳細信息,請參閱創建或刪除自定義數字格式。
注意:?這不會還原格式設置之前刪除的前導零。它只會影響在應用格式后輸入的數字。
使用 TEXT 函數應用格式
你可以使用數據旁邊的空列, 然后使用TEXT 函數將其轉換為所需的格式。
號碼代碼
示例 (在單元格 A1 中)
TEXT 函數和新格式
社交 債券
012345678
= 文本 (A1, “000-00-0000”) 012-34-5678
電話
0012345556789
= 文本 (A1, “00-0-000-0000”) 00-1-234-555-6789
郵政 區號
00123
= 文本 (A1, “00000”) 00123
向下舍入信用卡號碼
Excel 的最大精度為15個有效位, 這意味著對于包含16個或更多位數的任何數字 (如信用卡號), 任何超過15位的數字都將舍入到零。對于16位或更大的數字代碼, 必須使用文本格式。為此, 您可以執行以下兩項操作之一:
-
將列的格式設置為文本
選擇數據區域, 然后按Ctrl + 1以啟動 “>單元格格式” 對話框。在 “數字” 選項卡上, 單擊 “文本”。
注意:?這將不會更改已輸入的數字。它只會影響在應用格式后輸入的數字。
-
使用撇號字符
您可以在數字前面鍵入撇號 (‘), Excel 會將其視為文本。
返回頁首
需要更多幫助嗎?
可隨時在 Excel 技術社區中咨詢專家,在解答社區獲得支持,或在 Excel User Voice 上建議新功能或功能改進。