Excel - 怎樣把文字變回數字

Question

Excel file 內有以下的"文字", 怎樣可以把它變回數字, 當然想用 formula 一次過改啦.

Col A
'___ 1,234.00
'__ 567,789.01

Thanks you very much

請問 _" 是點解? 我查help, 查不到quot是什麼?

多謝各位給我這麼多的excel 知識, 本人有許多也未見過,實在要少許時間消化. 先多謝!

另外,BiGLiN 君, 是否有一個詳細的 Text 檔來解釋第二條公式或array formula. 如方便的請上傳,因我不太明白你們的公式了, 要了解 了解. Thanks

Answer 1

對不起，首先想說一下我對在 Excel 裏使用 VB 的看法。
用 VB 來做這種普通的問題實在是 overdone 了。
Excel 之所以是一格格，就是為了方便人們處理簡單的數字運算，只要輸入簡單的公式，用滑鼠按幾下，想要的結果就整齊地排列並呈現出來。
若每一件簡單的任務都用 VB 做，不理 Excel 本身的 function 能否容易地做到，那就失去使用 spreadsheet 原來的意義，倒不如真接用真正的 programming language 來做好了。
況且以上的 VB code 亦不對。

問題中的文字只要用 substitute() 移走 underscore ，然後用 value() 將文字轉做數字即可。

=VALUE(SUBSTITUTE(A1,"_",""))



圖片參考：http://big-lin.com/Documents/7007021300909_Excel_Remove_Underscore.JPG


2007-02-16 13:10:13 補充：
就算問題中的「文字」裏不只有 underscore ，還是可以用一條公式做到的：
=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$20),1)),0),2+COUNT(1*MID(A2,ROW($1:$20),1)))


2007-02-16 13:11:42 補充：
喔，對不起，剛剛的補充亂了碼。就算問題中的「文字」裏不只有 underscore ，還是可以用一條公式做到的：=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$20),1)),0),2+COUNT(1*MID(A2,ROW($1:$20),1)))注意這條是 array formula ，完成輸入時要按 Ctrl+Shift+Enter 而不是平時那樣按 Enter 就可以。

2007-02-16 13:23:13 補充：
Excel 檔下載：http://big-lin.com/Documents/7007021300909_Excel_Remove_Underscore.xls

2007-02-16 14:32:39 補充：
回美香小姐：多謝你的回應。其實，剛剛這條新公式比較長，而且要明白首先要熟悉 array formula ，這時候簡短的 VBA 或許比較優勝。若有朋友需要解釋，我可另寫一個詳細的 Text 檔上傳，這裏的補充功能每次限寫三百字太麻煩了。不過， VBA 也不是人人都懂，你提供了 code ，發問者應付了當前的問題，但若他本人不懂 VBA ，以後若要修改調校亦不易，就算是以上這條長公式，我也覺得還是比 VBA 容易掌握及學習。所以我認為，非不得已最好不要用 macro 。當然，甚麼時候是非不得已，就要用者自己決定了。

2007-02-16 14:33:12 補充：
另外，如我之前所說，你以上的 code 有問題，並不能用於題目裏的「文字」。我猜你測試時沒有用有逗號的數字，但發問者的題目裏是有逗號的。當你的 code 裏第二次遇上非數字 (如那個逗號) 時，就會停止，不能正確轉換。我建議可以改成以下這樣：

2007-02-16 14:33:51 補充：
Public Function MIDVALUE(v) As DoubleFor t = 1 To Len(v)If IsNumeric(Mid(v, t, 1)) ThenX = X & Mid(v, t, 1)End IfNextMIDVALUE = Val(X) / 100End Function最後，很高興與你交流心得，希望大家都有所得益。

2007-02-20 17:42:48 補充：
To: 阿nam真是厲害的公式。看了你的公式實在獲益不少！我的簡直是十倍 overdone 。有時想刪掉我的回答，哈哈！

2007-02-27 08:27:04 補充：
' ' ' ' ' ' To kkho66666,我對我的公式作了一些說明，希望對你了解我的公式有幫助，請下載以下 Excel 檔：http://big-lin.com/Documents/7007021300909_Excel_Remove_Underscore_Explained.xls我的公式比阿 nam 的累贅很多，只適合於學習 array formula ，不宜用於你的問題上。

2007-02-27 08:28:51 補充：
如阿 nam 所說，你可以用 Excel 中的 Evaluate Formula 來研究公式是怎麼一步一步計出答案的。

Answer 2

我都跟著用過BiGLiN提供嘅公式去做，真係幫到手呀！

Answer 3

可以試下輸入公式
B1輸入
=LOOKUP(9E+100,--RIGHT(A1,ROW($1:$100)))

2007-02-19 05:20:27 補充：
此公式 是以儲存格右方的數字作對應對應的儲存格 左方 可以為任何文字

2007-02-19 15:50:43 補充：
to 新田美香上列的公式是通用的美元 1,234.56, RMB987.65 可以用上面的公式找出數值33 pcs, 245只只要將right 改為left 也能得到數值就是我學的 分割儲存格兩端 數字的方法 (通用)

2007-02-19 17:45:31 補充：
你睇返函數說明lookup其中一個用法 同 sumproduct 一樣 都係對應陣列如果想理解 你自己睇返 工具>>公式稽核>>評估值公式lookup係尋找最後一個符合條件的數據

Answer 4

方法 - 自製Formula, 公式名稱: Midvalue
按 Alt + F11, 出現一個Visual Basic視窗, 按插入→選模組, 然後將下列的程式貼上, 關閉Visual Basic 視窗。
Public Function MIDVALUE(v) As Double
For t = 1 To Len(v)
If Mid(v, t, 1) Like “#” Then
X = Val(Mid(v, t))
Exit For
End If
Next
MIDVALUE = X
End Function

自製Formula完成

在B1輸入公式: = MIDVALUE(A1), 然後下拉
你要的數字便會自動出現

如有不明白, 請補充 或 留下E-MAIL

2007-02-16 10:48:17 補充：
回應樓下BiGLiN:每次我答問題, 如非題目要VBA, 我當然是以公式(最好可下拉)作為首選。如無法達成, 才會用輔助格+公式, 最後才用其他可行而實用的辨法, 如VBA

2007-02-16 10:49:20 補充：
以這條題目來說, 問者的Underscore應比喻是不同非數字及沒有固定數量, 所以 subsitute, mid, right, left 等文字串函數應無法解決問題。此問題是問得很好, 應用性非常高, 以我為例, 公事上經常收到大量的資料, 用文字格式輸入貨幣及單位, 如美元 1,234.56, RMB987.65, 33 pcs, 245只…等, 我每次都要用VBA去解決, 故此題我亦將我的經驗向大家分享。

2007-02-16 10:51:26 補充：
其實我都沒有學過電腦及VB, Excel 和 Excel vba 祇是我公事上要用Excel處理帳務/倉務/船務/打單等超過10年的經驗所累積, 遺憾是我至今都不懂用陣列公式我不是賣弄VB, 事實上亦沒有資格。

2007-02-16 19:05:28 補充：
多謝BiGLin君提點, 我應在code的第一句加上:v = Replace(v, “,”, “”)才能解決千位逗號的問題。本想發電郵向BiGLiN君致謝, 耐何BiGLiN的電郵尚未認證

2007-02-16 19:13:56 補充：
有關BiGLin君的code, 對 __12, __12.3, 及 __12.345 及任何非2位小數值都是會有錯誤的, 請注意

2007-02-19 17:18:09 補充：
阿nam, 你的公式好work, 嚴格來說, 我其實都唔明點解會咁。你用了row 傳回數值陣列(我前天看書才知這種宣告方法), 但又不是陣列公式, 可否教我你這條式是應定義為一般的公式, 還是陣列公式呢? 其他的, 我都不明, 但我希望我能自己去理解。

Answer 5

=VALUE(SUBSTITUTE(A1,"_" , ""))