☰

將包含「帶空格的文字數字」的儲存格轉換為可以計算的數字

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
將包含「帶空格的文字數字」的儲存格轉換為可以計算的數字

我是一名 Excel 初學者，花了一些時間來解決這個問題，但最終放棄了。

我有一個資料集，其中包含看起來數字的單元格，但這些“數字”不允許任何計算。我用谷歌搜索，發現那些看起來像數字的單元格實際上可能是“文本”（我猜它是某些編程語言中的某種字符串格式）。

然後我找到了一些方法將看似數字的文字轉換為實數。包括「文字到列」方法---失敗，以及「貼上特殊方法」---失敗。

看起來這是兩個問題的結合。一是這些單元格實際上是文本，二是這些單元格中的數字以空格結尾。在解決這個問題之前，請允許我更多地描述我為解決這個問題所做的努力。我還發現內建函數 VALUE() 可用於將文字轉換為其數值。我嘗試過但失敗了。然後我意識到這些看起來像數字的文字末尾有一個空格！因此，我使用內建函數 TRIM() 來取得一個新選項卡，而不會為每個看起來數字的儲存格新增空白。

但是，當我在新分頁中使用=VALUE(B3)（其中我已經使用 TRIM() 函數來消除任何空白）時，我仍然收到錯誤。你們能解釋為什麼我的試驗失敗了，為什麼我透過谷歌搜尋找到的方法也失敗了（「選擇性貼上」和「文字到列」）？

我什至嘗試關注這篇文章，但也失敗了... https://stackoverflow.com/questions/2741991/how-to-convert-text-to-numbers 任何幫助將不勝感激，謝謝！

答案1

NBSP 字元是一個常見問題，尤其是當文字來源是 HTML 文件（網頁）時。可以透過以下方式將其刪除

  =substitute(b3,char(160),"")

答案2

可以這麼說，更通用的解決方案可能是提取所有數字字符，並讓 Excel 從一開始就將結果視為數字。以下公式僅擷取“0-9”、“-”和“.”儘管如何添加其他內容或刪除這些內容非常明顯：

=VALUE(TEXTJOIN("",TRUE,IFERROR(UNICHAR(((UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<>47)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))>44)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<58)*1)*UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))),"")))

因此，無論是難以列出的空格和“不間斷”空格，還是任何其他字符，數據源都用於格式化其數據，甚至可能用於使您更難提取將它們的數據轉換成某種有用的形式，無論如何，只有數字“0-9”、負號和小數點被提取到帶有公式的單元格中（或轉換成直接使用輸出的某些公式），所以無論它們是什麼，它們不能再打擾你了。

有六種「常用方法」來解決這個問題，顯然上面的方法對於海報來說效果很好，但我至少根據經驗知道使用了十幾個字符，儘管因為我沒有保留列表，所以它是每次都用零碎的方式進行新的戰鬥。另外，這超出了F2-Edit-Enter和Paste|Special|Multiply (by 1)不起作用的情況......它們應該，但顯然 Excel 有時具有用戶無法編輯的雜散隱藏格式字元。沒關係，那些也被拋在後面。

相關內容