跳到主要內容

如何在Excel中將科學計數法轉換為文本或數字?

 
doc-convert-sentiific-to-text-1 -2 doc-convert-sentiific-to-text-2

一般來說,當您輸入長度超過11位的數字時,Excel會自動將其轉換為科學計數法。 這些科學符號非常令人討厭,因此您需要將其轉換為正常數字,如左圖所示。 您如何在Excel中解決此任務?

在數字前加單引號將科學計數法轉換為文本

使用格式單元格功能將科學計數法轉換為文本

使用公式將科學記數法轉換為文本

使用Kutools for Excel將科學計數法轉換為文本


箭頭藍色右氣泡 在數字前加單引號將科學計數法轉換為文本

輸入數字之前,可以先輸入單引號',並且數字不會成為科學計數法,請參見以下屏幕截圖:

doc-convert-sentiific-to-text-3 -2 doc-convert-sentiific-to-text-4

箭頭藍色右氣泡 使用格式單元格功能將科學計數法轉換為文本

如果您有很多數字以科學計數法顯示,並且厭倦了使用上述方法重複輸入數字,則可以使用 單元格格式 Excel中的功能。

1。 選擇要轉換的數據范圍。

2。 右鍵單擊,然後選擇 單元格格式 從上下文菜單中,查看屏幕截圖:

doc-convert-sentiific-to-text-5

3。 在 單元格格式 對話框,在 聯繫電話 標籤,點擊 習俗 來自 類別 列錶框,輸入數字 0 進入“類型”框,請參見屏幕截圖:

doc-convert-sentiific-to-text-6

4。 然後點擊 OK 按鈕,並且顯示為科學計數法的單元格編號已轉換為普通編號。


箭頭藍色右氣泡 使用公式將科學記數法轉換為文本

以下公式也可以幫助您將科學計數列表轉換為文本。

1。 請輸入以下公式: =修剪(A1) 放入空白單元格(例如B1)中,請參見屏幕截圖:

doc-convert-sentiific-to-text-7

2。 然後將填充手柄拖到您要應用此公式的範圍內,您將獲得所需的結果:

doc-convert-sentiific-to-text-8

注意: 這個 功能: = UPPER(A1) 也可以為您提供幫助,請根據需要使用任何一種。


箭頭藍色右氣泡 使用Kutools for Excel將科學計數法轉換為文本

如果你已經安裝 Excel的Kutools,你可以使用 在文字和數字之間轉換 功能來完成這項工作。

Excel的Kutools : 帶有300多個便捷的Excel加載項,可以在30天內免費試用. 

安裝後 Excel的Kutools,請這樣做:

1。 突出顯示要轉換的數據范圍。

2。 點擊 庫工具 > 內容 > 在文字和數字之間轉換,請參見屏幕截圖:

3。 在 在文字和數字之間轉換 對話框,檢查 文字編號 選項,然後單擊 OK or 登記 按鈕,顯示為科學計數法的數字已轉換為原始範圍內的普通數字。 看截圖:

doc-convert-sentiific-to-text-10

提示:

如果您的電話號碼長度超過15位數字,則不會根據需要將其轉換為完整的電話號碼。 您只需要在輸入數字之前輸入單引號或將所選範圍設置為 文本 格式在 單元格格式 輸入數字之前,請先輸入對話框。

doc-convert-sentiific-to-text-11

要了解有關此在文本和數字之間轉換功能的更多信息。

立即下載並免費試用Excel的Kutools!


箭頭藍色右氣泡 演示:使用格式單元格功能將科學計數法轉換為文本

Excel的Kutools:具有300多個方便的Excel加載項,可以在30天內免費試用,沒有任何限制。 立即下載並免費試用!

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
No meu caso, os dados já são exportados e já vêm em formato de notação científica, é um relatório com muitos dados e eles já vêm escrtos incorretamente, com zeros no final. Teria como recuperar os valores reais estes números.
This comment was minimized by the moderator on the site
I can only comment this on this content.
i Lovvvvvvve you.Ummmmmmmah
This comment was minimized by the moderator on the site
Hello Matchxchange,I am so glad you like our article. Thank you a lot!Sincerely,Mandy
This comment was minimized by the moderator on the site
I was looking for an easy way to convert scientific notation to Text format. On your page I found what I needed to know. Many Thanks!
This comment was minimized by the moderator on the site
Thank you so much!
This comment was minimized by the moderator on the site
Is there a way to copy it without making it a screenshot? cause when i select it, it goes back to the scientific notation
This comment was minimized by the moderator on the site
Is there a way to recover lost leading zeros?
This comment was minimized by the moderator on the site
Perfect answer. The screenshots made it pretty easy to understand
This comment was minimized by the moderator on the site
Its easy. please follow the below steps: 1) Copy the Row/Column(Containing digits more than 15) and paste it on a notepad. 2) Select the row/Column in excel where data needs to be pasted and format it in the "Text". 3) Copy the data from notepad, right click on the row/column it needs to be pasted and then paste it as paste special. Problem solved.
This comment was minimized by the moderator on the site
NOT SOLVED. INEED TO WRITE 877211E000 BUT ITS SHOWING AFTER WRITING 8.77E+05?????

PLEASE HELP
MAIL ID
ph 9414011457
This comment was minimized by the moderator on the site
WHY HAS THIS NOT BEEN RESOLVED YET?

I agree it is not solved, I have set the column formatting to text for writing in room numbers e.g. 05E22 and EXCEL RE-WROTE it as 5.00E+22
so EXCEL IS CORRUPTING MY DATA ON ITS OWN !!! :(
Furthermore, as I am often forced to generate room numbers from a different cell, I need the column to be formatted as general so I can use formulas to capture parts of other cells, so this workaround is MICROSOFT & EXCEL incompetence
This comment was minimized by the moderator on the site
It took me a minute to figure it out. So far this is the best way I could find as I had issues with those trailing zeros that no one was able to address.
Open File in Notepad and SaveAs .txt
Import .txt File into Excel
Open blank Excel workbook
Data > From Text/CSV (Keyboard shortcut Alt A, FT)
Select .txt File that you saved earlier > Load
Excel will import data as a table but you will notice that all those numbers that previously had an issue will be expanded out as a regular number. Hope this helps!
This comment was minimized by the moderator on the site
Was looking for an easy way to convert scientific notation to Text format easily. Your page was perfect for what I needed to know and very straight forward. I will bookmark this page for future reference. Excellent work!
This comment was minimized by the moderator on the site
Perfect.. saves lot of time.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations