題 為什麼即使將單元格格式更改為文本後Excel也將長數字字符串視為科學記數法


我正在嘗試在Excel中處理一些數據。數據包括數字帳號和其他長數字字符串(如手機MEID)。我沒有對這些字符串進行數學運算,所以我希望Excel將它們視為純文本。

這就是讓我瘋狂的原因(這是Excel 2010):

  1. 拿一個像1240800388917這樣的長數字並將其粘貼到新工作表的單元格中。
  2. Excel的默認單元格格式是通用的,因此字符串以科學計數法表示為1.2408E + 12
  3. 右鍵單擊單元格,選擇“設置單元格格式”,將格式設置為“文本”

即使格式已設置為文本,該單元格仍以科學計數法顯示。

現在,如果我按照不同的順序執行這些步驟:

  1. 將空單元格格式化為文本。右鍵單擊單元格,選擇“設置單元格格式”,將格式設置為“文本”
  2. 拿一個像1240800388917這樣的長數字並將其粘貼到文本格式的單元格中

現在,單元格顯示為字符串,而不是科學記數法。

即使單元格被格式化為文本,結果仍然保留在科學記數法中,這對我來說似乎很難理解。我見過建議的解決方法,例如:使用CSV導入並將格式設置為文本,在每個數字字符串的開頭添加空格字符,以及其他。

是否有一個簡單的好工作,可以輕鬆地將這些字符串格式化為文本?

Excel為什麼要這樣做?

我發現相關的SU問題: 如何使Excel 2007停止將大數字格式化為科學記數法? 和 這個Excel行為是否具有較大的十六進制數?


94
2018-04-16 20:39


起源


長號 1240800388917 是從另一個單元格複製?或者只是純文本,比如記事本? - wilson


答案:


啊,當我在Excel中進行一些大量的數字交叉檢查時,數據回憶起來(從未再次)..當你將長數字字符串打入Excel時,試試說,12345678901234567890(20位),Excel一般會 兌換 它適合你,這意味著你剛剛使用的20位數字已被削減為僅有十五位有效數字。

還要注意:轉換是截斷,而不是捨入。 (12345678901234567890是1.23456789012346E + 19,但excel會告訴你1.2345678901234E + 19)

這發生在入口點,所以一旦它進入,任何額外的細節都會丟失,所以即使你告訴Excel你真的意味著文本而不是數字,你有點不走運,因此為什麼你的第一個序列不起作用。


20
2018-04-16 23:45



是的,當我的數字字符串超過15位有效數字時,我看到了這種行為。 12345678901234567890變為1.23456789012345E + 19如你所說。當我單擊單元格時,編輯框會顯示截斷的字符串,並用零填充為12345678901234500000.奇怪的是,它對於不會溢出excel的15位數限制的數字執行相同的操作。如果我粘貼在12345678901234中,它將顯示為1.23457E + 13,但是當我單擊它時,編輯框仍會顯示原始字符串。現在,奇怪的是如果我將這個單元格格式化為文本,它仍然以科學記數法顯示。煩人。 - Michael Levy
那個實際上是一個顯示怪癖 - 將格式從一般設置為文本不會立即將其顯示為原始字符串,但如果您進入並編輯數據(如同,只需單擊進入編輯區域並單擊)它會更新為你輸入的內容。 - tanantish
如果我點擊單元格,我可以看到excel有整個數字,但無論我是否將所有單元格文本生成,它仍然想要使用科學記數法。真令人沮喪! - The Muffin Man
快速黑客:在適當的列上設置格式,將粘貼複製到記事本,然後再复製到Excel。 - Brian
這應該 不是被接受的答案:見下面的下一個。 - javadba


這在Excel 2010中適用於我。只需選擇列,右鍵單擊,格式化單元格,自定義並選擇顯示0的選項(常規下面的第二個選項)。


136
2017-07-23 10:53



這比接受的答案要好得多,這句話說“你運氣不好”!我有一個CSV文件有這個問題,自定義格式化解決方案工作得很好。 - davidair
必須接受答案,有人應該/可以改變嗎? - qwertzman
這仍然在我的測試中被截斷。使用Excel 2013我在單元格12345678901234567890中輸入以下內容。當我按Enter鍵時,顯示為1.23457E + 19,基礎值更改為12345678901234500000,因此存在數據丟失。這不符合原始問題。 - Michael Levy
在這裡,我在一年後重新審視同樣的答案,並希望我能再次投票。 - billynoah
此答案修復了格式,但不保留輸入的數據。這就是我不能接受它為正確答案的原因。我發布了一個簡短的視頻來展示。當您按照這些方向,然後輸入像12345678901234567890這樣的長整數時,即使以非科學記數法顯示,也會在將其視為數字時丟失精度。 Excel將其視為值12345678901234500000。只有將其視為文本才能保留所有數字。看到 youtu.be/7EDcR7TQpYs - Michael Levy


數字前面的單個撇號會強制Excel將數字視為文本(包括默認的左對齊)。如果您標記了錯誤,它將顯示為單元格中存儲為文本錯誤的數字。


12
2018-04-16 21:01



這並非總是如此。 Excel有時甚至會使用一個主要的撇號來提取我的數據。 - Cerin
Cerin,一個公平的觀點 - 永遠不要低估Excel(或微軟)做一些完全無法解釋或可重複的事情的能力。然而,撇號技巧通常如宣傳的那樣有效。 - dav
你能批量做這件事嗎?我有60000個單元格的數據,我想轉換為文本。 - vaibhavcool20


我發現在Office 2010中,如果先格式化列,然後粘貼數據,它將正確顯示大數字。


10
2017-09-28 16:44



這可能是因為它存儲為字符串。如果您製作另一個單元格,例如單元格的內容乘以2,會發生什麼? - Peter Mortensen
這適用於Excel 2016 / Office 365.我選擇了所有單元格,將它們格式化為文本,然後粘貼我複制的數據,並且它沒有顯示惱人的科學記數法。謝謝! - frezq


試試這個...這適用於我,Excel 2013。

=""&a1

其中a1包含數值。 之後,複製列並粘貼到原始列。


10
2017-07-05 07:44



這也適用於Excel 2010。 - Paul G
作為側節點: ="1240800388917" 做同樣的事 - nixda


您可以將單元格格式化為Custom>#

將數據粘貼到列中後可以應用此選項,但必須在保存電子表格之前執行此操作。

我使用條形碼(UPC)工作很多,默認情況下,excel會將它們格式化為科學記數法。讓我瘋狂!為什麼這不是我永遠不會知道的選擇。


4
2017-07-02 15:02



但這並沒有將數字轉換為文本,這可能會在以後引起問題。但是...... - Joris Meys


Excel 2010:它一次只能處理一列,而對於一行則根本不起作用。選擇列的列或子集,從“數據”選項卡中選擇“文本到列”,然後向右跳到“完成”。沒有更科學的記號。

我同意許多Excel用戶的觀點,認為沒有防止科學記數法的選擇是犯罪行為!


3
2017-08-14 22:15



在我對Excel 2007的測試中,我不得不點擊 Next > 兩次,然後選擇“文本”並單擊 Finish。您可以通過轉置,轉換和轉置將其應用於行。 - Scott


我知道這個問題的年齡,但這是我在google搜索後登陸的那個並沒有回答我的問題(為什麼格式化為文本不起作用以及為什麼數字在格式化後減少到15位數到文本)。

簡短的回答是:輸入數字並點擊後,您無法按照自己喜歡的方式處理數字 Enter。您這樣做的那一刻,該數字將被截斷為15位數並以指數形式呈現。

這不是一個錯誤,它是一個功能。

你之後做的任何事情都將使用截斷值,因此沒有格式化技巧會有所幫助。 但是,你可以使用 Text to columns 將指數表示法轉換為文本的選項(單擊列標題,單擊 Text to data, 然後 DelimitedNext,取消勾選所有分隔符框,選擇 Text 在 Column data format 然後 Finish)將值轉換為文本並立即顯示為15位數字。但是,它只有15位,所以如果你的號碼更長,那麼休息就會丟失。

為了獲得電子表格中的數字 究竟 你鍵入它的方式必須將其存儲為文本。因此,您必須在其前面加上撇號或將單元格格式化為文本 之前 在其中鍵入/複製值。如果它“有時不起作用”,那麼,抱歉,你做錯了或者你有某種自動更正(如果你使用撇號和大數> 15位數Excel將其視為錯誤的值並標記單元格與警告消息,所以這句話不是個人的,也不是批評的。

在批量編號中執行此操作的其他方法是從文本文件導入值。我敢說這是大多數情況下的唯一途徑。並確保導入文件,而不是像Excel一樣打開它 csv 類似於它的原生格式,並將大數字修剪為15位數當然是理所當然的。

現在,到最後一期。如果在格式化為文本的Excel單元格中輸入大數值,只要單元格不夠寬,它仍將以科學計數法顯示。這很麻煩,但是一些Excel內部算法的副作用。只需讓細胞更寬,每次都會看到完整的價值。


3
2017-11-03 17:38





在Excel 2013中,同樣的事情發生在我身上,我只是去了格式化單元格 - >數字,然後確保小數位設置為“0”。


1
2017-10-21 19:48



類似於上面的匿名回答,但選擇“數字”在某種程度上比選擇“自定義”更有意義..加上它在列表中更高。無論如何,它做同樣的事情,但我更喜歡這個 - billynoah


選擇所有單元格 - 或者,為了方便起見 - 選擇整個電子表格並單擊 格式化單元格, 然後 文本

您的整個電子表格會自動使用文字。您不必逐個單元地執行此操作。


0
2018-04-17 05:40



唉,這不起作用。文本只是科學記法,這就是整個問題...... - Joris Meys


  1. 將問題字段複製/粘貼到A列的新工作表中

  2. 將A列複製/粘貼到文本文件中

  3. 格式化列B以將文本文本粘貼到列B中

  4. 在C欄:

    =IF(ISNUMBER(SEARCH("+",B1)),A1,"")
    
  5. 將C列轉換為沒有小數的數字格式

  6. 將C列複製/粘貼到文本文件中

  7. 將D列格式化為文本並將文本文件粘貼到D列

  8. 在E欄:

    =IF(IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2))=0,"",IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2)))
    
  9. 將E列複製/粘貼到記事本中

  10. 刪除問題字段中的數據並將列格式化為文本

  11. 將數據從文本文件複製/粘貼到問題字段


0
2017-09-27 18:39