Excel - 怎樣把文字變回數字

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

Col A

'___ 1,234.00

'__ 567,789.01

Thanks you very much

更新:

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

更新 2:

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

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

5 個解答

評分
  • BiGLiN
    Lv 6
    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_R...

    2007-02-16 13:10:13 補充:

    <big><big><span style="font-family:Calibri;">就算問題中的「文字」裏不只有 underscore ,還是可以用一條公式做到的:</span><br><span style="font-family:Calibri;">=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$20),1)),0),2+COUNT(1*MID(A2,ROW($1:$20),1)))</span><br></big></big>

    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_R...

    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 補充:

    &#39; &#39; &#39; &#39; &#39; &#39; To kkho66666,我對我的公式作了一些說明,希望對你了解我的公式有幫助,請下載以下 Excel 檔:http://big-lin.com/Documents/7007021300909_Excel_R... nam 的累贅很多,只適合於學習 array formula ,不宜用於你的問題上。

    2007-02-27 08:28:51 補充:

    如阿 nam 所說,你可以用 Excel 中的 Evaluate Formula 來研究公式是怎麼一步一步計出答案的。

  • 1 十年前

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

  • 1 十年前

    可以試下輸入公式

    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 一樣 都係對應陣列如果想理解 你自己睇返 工具&gt;&gt;公式稽核&gt;&gt;評估值公式lookup係尋找最後一個符合條件的數據

  • 1 十年前

    方法 - 自製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 傳回數值陣列(我前天看書才知這種宣告方法), 但又不是陣列公式, 可否教我你這條式是應定義為一般的公式, 還是陣列公式呢? 其他的, 我都不明, 但我希望我能自己去理解。

  • 你覺得這個解答怎樣?你可以登入投選解答。
  • 1 十年前

    =VALUE(SUBSTITUTE(A1,&quot;_&quot; , &quot;&quot;))

還有問題嗎?立即提問即可得到解答。