EXCEL 儲存格字串中取出數字並加總

呼 若數字最多五字元

，且不在最右

即

AAA12BB3CC6.5DDD7.12

請改為

AAA12BB3CC6.5DDD7.12g

則小弟想到一解(無定義、無VBA)

2009-02-11 22:22:04 補充：

=SUMPRODUCT(--IF(

(ISNUMBER(--MID(A1,ROW($2:$29),COLUMN($A:$E)))=TRUE)*

(ISNUMBER(--MID(A1,ROW($2:$29),1+COLUMN($A:$E)))=FALSE)*

(ISNUMBER(--MID(A1,ROW($1:$28),COLUMN($A:$E)))=FALSE)*

2009-02-11 22:22:17 補充：

(RIGHT(MID(A1,ROW($2:$29),1+COLUMN($A:$E)))<>".")*

(LEFT(MID(A1,ROW($1:$28),1+COLUMN($A:$E)))<>"."),

MID(A1,ROW($2:$29),COLUMN($A:$E)),0))

2009-02-11 22:22:32 補充：

兩個意見的公式請合起來

2009-02-11 22:24:14 補充：

如附件(Q-piny)

http://www.funp.net/338288

請各位大師抽空測試 ^^

2009-02-11 22:26:31 補充：

(LEFT(MID(A1,ROW($1:$28),1+COLUMN($A:$E)))<>"."),

可簡化為

(LEFT(MID(A4,ROW($1:$28),1))<>"."),

2009-02-11 22:38:11 補充：

把公式中之A1都改成A1&"a"

可解決數字剛好在最右的情況

2009-02-11 22:52:52 補充：

更新附件(Q-piny)

100字元以內不限項目

http://www.funp.net/332123

2009-02-12 09:33:12 補充：

呼 很高興可以參與討論

=SUMPRODUCT(--IF(

(ISNUMBER(--MID(A2&"a",ROW($2:$91),COLUMN($A:$Z)))=TRUE)*

(ISNUMBER(--MID(A2&"a",ROW($2:$91),1+COLUMN($A:$Z)))=FALSE)*

(ISNUMBER(--MID(A2&"a",ROW($1:$90),COLUMN($A:$Z)))=FALSE)*

(RIGHT(MID(A2&"a",ROW($2:$91),1+COLUMN($A:$Z)))<>".")*

(LEFT(MID(A2&"a",ROW($1:$90),1))<>"."),MID(A2&"a",ROW($2:$91),COLUMN($A:$Z)),0))

公式說明，先利用IF函數，將符合所需的保留，餘皆視為0，之後再用SUMPRODUCT去加總

如何找出符合所需，在此利用從該格第二字元開始一次數若干字元（即COLUMN($A:$Z)），當下列四個條件同時條件才算為真

１．該字串為數字

２．該字串若再加一字元就不為數字

３．該字串若再加一字元，而該字元不為「.」

４．該字串之前一字元不為「.」

2009-02-12 09:37:06 補充：

更正

．．．當下列四個條件同時條件才算為真．．．

應為

．．．當下列四個條件同時成立才算為真．．．

2009-02-12 10:43:46 補充：

再更正並詳述

並舉一例（即若從第三字元取五個時，要過下面全部關卡才算成立

１．該字串為數字（即從第三字元取五個為數字）

２．該字串若再加一字元就不為數字（即從第三字元取六個需不為數字方成立）

３．該字串同長度若往前一字元則不為數字（即從第二字元取五個需不為數字方成立）

４．該字串若再加一字元，而該字元不為「.」（即從第三字元取六個，則第六個之字元不為「.」）

５．該字串之前一字元不為「.」（即第二字元不為「.」）

2010-08-21 07:59:56 補充：

從網路上不巧看到的妙解 與大師分享

=SUM(TEXT(LEFT(TEXT(MID(A2&"a",COLUMN(2:2),ROW($1:$15)),),ROW($1:$15)-1),"0%;-0%;0;!0")*ISERR(-MID(A2,COLUMN(2:2)-1,2)))

麻煩請你去回答一下

15點要送出去

請問你有信箱嗎？

以後有問題可以請教你

你會VBA與巨集嗎

2010-05-04 22:36:03 補充：

Vincent 大師 你好

我有發問

在次請你幫忙

謝謝 感恩

希望各位vba大師們可以寫個vba當番外篇，給大家參考一下了。

小弟今晚有空也寫一個。

2009-02-11 13:37:50 補充：

就我對此題的見解最多一個數只有一個負號

牛奶-1.2公升/咖啡1.3L/柳橙汁-20升/酸梅汁3.45L

不會有下列的

-+1.2或10%或.5或.5.5或 3*10^2或3E2或新田兄提的這些問題

單純只是數字相加，那此vba各位大師會怎解？

建議v兄建一個範本給大家參考及所需要的答案了~~

2009-02-11 17:29:22 補充：

Function Summ(rng As Range) As Double

Dim b#, t$, s$, i%, a

t = rng

For i = 1 To Len(rng)

s = Mid(rng, i, 1)

Select Case s

Case 0 To 9

Case ".", "-"

Case Else

t = Replace(t, s, " ")

2009-02-11 17:30:33 補充：

End Select

Next

a = Split(t)

For i = 0 To UBound(a)

If a(i) <> "" Then b = b + CDbl(a(i))

Next

Summ = b

End Function

有符合v大上傳的資料，其他要加什麼再修了。

2009-02-11 23:03:16 補充：

piny 兄解的好~~

Vincent 兄可能需要一個類似 parser 的程式, 用 Basic 不好寫, 但可以試試, 可是你說 "不以 VBA" 就......

2009-02-11 14:26:57 補充：

Option Base 1

Function IsDigit(Char As String) As Boolean

Dim r As Variant

r = InStr(1, "0123456789.", Char, vbTextCompare)

If r <> 0 Then

IsDigit = True

Else

IsDigit = False

End If

End Function

----待續

2009-02-11 14:28:20 補充：

Function SumNum(SrcStr As String)

Dim NumStr(10) As String, x As String

Dim i As Integer, j As Integer, k As Integer

j = 0

GotStart = False

----待續

2009-02-11 14:29:15 補充：

For i = 1 To Len(SrcStr)

x = Mid(SrcStr, i, 1)

If IsDigit(x) = True Then

If Not GotStart Then

GotStart = True

j = j + 1

End If

NumStr(j) = NumStr(j) & x

ElseIf GotStart Then

GotStart = False

End If

Next i

----待續

2009-02-11 14:30:04 補充：

For k = 1 To j

SumNum = SumNum + CDbl(NumStr(k))

Next k

End Function

用法:

=SumNum(""牛奶1.2公升/咖啡1.3L/柳橙汁20升/酸梅汁3.45L"")

2009-02-11 14:37:23 補充：

VBA 的解法範例檔:

http://www.funp.net/694214

答案在 D 欄

如:

文字1E2文字-1文字1,234文字1.23%文字2001/2/3文字12:00文字08:30 AM文字

再加上Excel的自作聰明的智慧, 更難預設客觀的唯一答案

由于用函數在一格內截取不知項及不知長度的數字段, 生成陣列再作運算, 存在極大的困難, 是否可以將題目改成容許一個輔助格, 或將項數限制為最多1個, 或 兩個, 或三個呢? 以便學習

2009-02-11 01:20:13 補充：

參考Triump知識長的公式思路, 不自量力, 優化為73個字元

=-LOOKUP(1,-MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&5^19)),ROW($1:$99)))

2009-02-11 01:32:01 補充：

用錯了詞, 不是優化, 只是簡短

如公式下拉過千格, Triump的原公式應更高效

2009-02-11 12:45:31 補充：

如寫vba Function, 亦需先將數字定義

如 1.1.1 ; 0.1.02 ; .1.22.1 ; .1

1,234 ; 1,234,4 ; 1,2,3,4

-1 ; --1 ; -+1 ; 1% ; 1%%

當然不應再考慮時間值, 運算符, 科學記號

2009-02-11 16:33:21 補充：

簡單做了一個Function

Function nsum(t) As Double

100:

If Len(t) = 0 Then Exit Function

tt = Val(t)

If tt <> 0 Then

nsum = nsum + tt

t = Mid(t, Len(tt) + 2, 999)

Else

t = Mid(t, 2, 999)

End If

GoTo 100

End Function

2009-02-11 16:47:56 補充：

如不接受負數, 可將 if tt<>0 改成 if tt>0

--7 視為-7

1,234視為 1 + 234

2%視為2

暫找到1個bug, 小數之后是%(如1.5%)出錯, 如有需要, 可置頂加一句:

t=replace(t,"%","|")

2009-02-12 01:56:58 補充：

回應樓主, 如用一格無瑕疵公式, 兩項我都無辦法, 希望能在此學習

難!

明天再想

2009-02-11 11:46:42 補充：

如可名稱定義

=SUMPRODUCT(--(MID(A1,AA,BB))),可解決

1個儲存格不知是太多層公式還是什麼,(公式有錯誤)

感謝T兄

需要同時提出所有數字，這就是所困惑的

2009-02-10 23:25:08 補充：

苦思幾天，無法解答，特求助諸位高手~

2009-02-11 12:16:48 補充：

小弟有一公式，但如同applerot大師所說，因為EXCEL2003公式超過7層，無法解出

至少需一個定義名稱

不知是否有不需定義名稱的解法，真是好難~

2009-02-11 14:00:03 補充：

小弟所見的應該是只計算以下數字

0、1、2、3、4、5、6、7、8、9、.

即為包含小數點之數字，長度不超過5個字元(含小數點)

至於其他符號，如 +、-、*、/、E、^、%.....

因為已太過複雜，就暫時不考慮了~

問題檔案

http://www.funp.net/730957

擷取數值公式：

=LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

2009-02-10 23:18:50 補充：

沒有特定區隔符號，高難度。

會有 1.2.3 這類東東嗎 ?

2009-02-10 22:55:53 補充：

抑或 3*10^2, 3E2

這類東東 ?

2009-02-10 23:08:00 補充：

水.2

這樣算 0.2 嗎 ?

2009-02-10 23:26:00 補充：

可以用巨集工作表嗎 ?

哈哈 ～ 開玩笑的啦 ～

2009-02-10 23:37:15 補充：

V兄問題太難 ～

1.2 抓到 1, 1. , .2 , 1.2 , 2

共 5 個數字 ... ˊ.ˋ ～

好難 ～

贊助看戲實在些 ～ 哈哈 ～

2009-02-11 02:01:31 補充：

5^19 真有創意 ～

用 7^18 或者稍快 ...

或 12^15 或者更快, 但多個字元 ～

2009-02-12 00:05:12 補充：

不才自以為寫出一個通解 ～

分段作都 OK ...

串起來 576 個字 ～ 被 EXCEL 說錯誤 ～

只好再想想 ～

2009-02-12 00:19:49 補充：

另外 ～ 對純程式碼有興趣的可以參考 :

Function SS(SO As String, Optional V) As Single

If SO = "" Then Exit Function

V = Val(SO)

SS = V + SS(Right(SO, Len(SO) - Len(V)))

End Function

2009-02-12 00:25:53 補充：

更嚴謹的寫法 :

http://tw.myblog.yahoo.com/jw!_DIL5waFHwVKfL8odMZ4...

2009-02-12 01:24:56 補充：

看了 piny兄 的解 ～

解題邏輯接近, 就不多獻曝了 ～