保持前置字元為零和大型數字 (機器翻譯)

保持前置字元為零和大型數字 (機器翻譯)

您是否曾經在包含前導Excel 00123 或 1234 5678 9087 6543 等大型數位的 Excel 中輸入或輸入資料? 例如,社會安全號碼、電話號碼、信用卡號碼、產品代碼、帳戶號碼或郵遞區號。 Excel移除前置字元為零,並將大型數位轉換成科學標記法 ,例如 1.23E+15,以便公式和數學運算能夠處理。 本文將討論如何將您的資料保留為原始格式,Excel視為文字。

當您輸入文字資料時,將數位轉換成文字

使用 Excel Power Query &轉換 (Power Query) ,在資料匯出時將個別欄格式化為文字。 在此案例中,我們正在輸入文字檔,但從其他來源 ,例如 XML、Web、JSON 等所匯出的資料,資料轉換步驟是相同的。

  1. 按一下 [資料>選項卡,然後按一下 [取得資料> 按鈕旁的 [從文字/CSV。 如果您沒看到取得資料按鈕,請前往新查詢>從檔案>文字,然後流覽至您的文字檔,然後按Import鍵。

  2. Excel載入您的資料至預覽窗格。 在 預覽 窗格中按 Edit 以載入 查詢編輯器

  3. 如果需要將任何欄轉換成文字,請按一下欄標題,選取要轉換的欄,然後選取 >轉換 > 資料類型>文字

    Power Query - 轉換成文字後的資料

    提示: 您可以使用 Ctrl+滑鼠左鍵選取多個欄

  4. 接下來,按一下 [變更欄類型Excel中的 [取代目前資料行Excel會將選取的欄轉換成文字。

    取得&轉換>將資料轉換成文字
  5. 完成後,按一下 [關閉並載入& ,Excel查詢資料會回到您的工作表。

    如果您日後的資料變更,您可以前往資料與重新>,Excel自動更新您的資料,並針對您進行轉換。

在 Excel 2010 和 2013 中,有兩種方法可以輸入文字檔,並將數位轉換成文字。 建議的方法是使用 Power Query,如果您下載 Power Query 附加元件,可以使用 Power Query。 如果您無法下載 Power Query 附加元件,您可以使用文字 輸入精靈。 在此案例中,我們正在輸入文字檔,但從其他來源 ,例如 XML、Web、JSON 等所匯出的資料,資料轉換步驟是相同的。

  1. 按一下功能 區中的 [Power Query> 索引鍵, 然後選取 [從文字> 外部資料

  2. Excel載入您的資料至預覽窗格。 在 預覽 窗格中按 Edit 以載入 查詢編輯器

  3. 如果需要將任何欄轉換成文字,請按一下欄標題,選取要轉換的欄,然後選取 >轉換 > 資料類型>文字

    Power Query - 轉換成文字後的資料

    提示: 您可以使用 Ctrl+滑鼠左鍵選取多個欄

  4. 接下來,按一下 [變更欄類型Excel中的 [取代目前資料行Excel會將選取的欄轉換成文字。

    取得&轉換>將資料轉換成文字
  5. 完成後,按一下 [關閉並載入& ,Excel查詢資料會回到您的工作表。

    如果您日後的資料變更,您可以前往資料與重新>,Excel自動更新您的資料,並針對您進行轉換。

使用自訂格式來保留前導數零

如果您想要在活頁簿中解決此問題,因為其他程式不會使用它做為資料來源,您可以使用自訂或特殊格式來保留前置字元為零。 這適用于少於 16 位數的數位代碼。 此外,您也可以使用虛線或其他標點符號來格式化您的數位代碼。 例如,若要讓電話號碼更可讀,您可以在國際代碼、國家/地區代碼、區碼、首碼和最後幾個號碼之間新增破折號。

數位代碼

範例

自訂數字格式

社交
安全

012345678

000-00-0000
012-34-5678 

手機

0012345556789

00-0-000-000-0000
00-1-234-555-6789 


郵寄 代碼

00123

00000
00123 

步驟    

  1. 選取要格式化的儲存格或儲存格範圍。

  2. Ctrl+1 以載入 [ 儲存格格式> 對話方塊。

  3. 選取 [數位」 選項卡,然後在 [類別清單上,按一下 [自訂>,然後在 [類型」 方塊中輸入數位格式,例如000-00-0000表示社會安全號碼碼,或00000表示五位數郵遞區號。

    提示: 您也可以按一下 [特殊資料,然後選取郵遞區號+ 4、電話號碼社會安全號碼

    尋找自訂代碼詳細資訊,請參閱建立 或刪除自訂數位格式

    附註: 這不會還原格式化前移除的前導數零。 它只會影響在採用格式之後所輸入的數位。

使用 TEXT 函數來適用格式

您可以使用資料旁的空白欄,並使用 TEXT 函數將其轉換為您想要的格式。

數位代碼

儲存格 A1 (中的範例)

TEXT 函數和新格式

社交
安全

012345678

=TEXT (A1,"000-00-0000")
012-34-5678

手機

0012345556789

=TEXT (A1,"00-0-000-000-0000")
00-1-234-555-6789


郵寄 代碼

00123

=TEXT (A1,"00000")
00123

信用卡號碼四捨五入

Excel的精確度上限為 15 個有效位數,這表示對於包含 16 位數以上數位的任何數位 ,例如信用卡號碼,第 15 位數之後的任何數位會舍位為零。 如果數位代碼是 16 位數或更大,則必須使用文字格式。 若要這麼做,您可以執行兩項操作之一:

  • 將欄格式化為文字

    選取您的資料範圍,然後按Ctrl+1以啟動 [儲存格格式>對話方塊。 在 [ 數位」 選項卡 上,按一下 [文字

    附註: 這不會變更已經輸入的數位。 它只會影響在採用格式之後所輸入的數位。

  • 使用單引號字元

    您可以在數位前面輸入 (') 單引號,Excel就會視為文字。

頁面頂端

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

需要更多協助?

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×