將儲存格中的資料擷取數字部分並加總

儲存格同時有文字與數字

ispiration 2020-04-30 15:47:40 ‧ 1792 瀏覽

分享至

大家好
因為網路上找不到適當的範例所以又在這裡求救啦
我有一個excel檔，每個儲存格都有文字與數字，數字又有千位逗點
我現在需要把每一列儲存格的所有數字擷取出來並加總

請問要如何撰寫VBA 或是現有ecel公式可以解決

高手路過別錯過先謝謝各位不吝分享

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2020-04-30 17:43:01

最佳解答

假設文字在 A1
把游標停在 B1 的地方
執行巨集 main
成功的話會在 C1,D1(,E1)出現數字
不成功的話
就另請高明

Sub Main()
    Dim str, sOneChar As String
    Dim sNumber As String
    Dim isNumber As Boolean
    Dim nCount As Integer
    nCount = 0
    isNumber = False
    sNumber = ""
    str = ActiveCell.Offset(0, -1)
    For nI = 1 To Len(str)
        sOneChar = Mid(str, nI, 1)
        If isYourNumber(sOneChar) Then
            sNumber = sNumber & sOneChar
            isNumber = True
        Else
            If isNumber Then
                ActiveCell.Offset(0, nCount).Value = stringToValue(sNumber)
                nCount = nCount + 1
                sNumber = ""
                isNumber = False
            End If
        End If
    Next nI
End Sub
Function isYourNumber(ByVal ch As String) As Boolean
    isYourNumber = False
    If ch = "0" Or ch = "1" Or ch = "2" Or ch = "3" Or ch = "4" Or ch = "5" Or ch = "6" Or ch = "7" Or ch = "8" Or ch = "9" Or ch = "," Or ch = "." Then
        isYourNumber = True
    End If
End Function
Function stringToValue(ByVal str As String) As Integer
    Dim s As String
    s = Replace(str, ",", "")
    stringToValue = Val(s)
End Function

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

ispiration iT邦新手 5 級 ‧ 2020-04-30 22:00:02 檢舉

太感謝了有一點bug 但是我修了一下就可以運行了
就是把 dim s as string 改成 long

謝謝喽

海綿寶寶 iT邦大神 1 級 ‧ 2020-04-30 22:36:27 檢舉

如果 OK 了就選個最佳解答把問題結案
還有你之前的兩個問題也是

ispiration iT邦新手 5 級 ‧ 2020-04-30 23:06:34 檢舉

海綿寶寶你好
感謝不吝分享代碼
因為上面的代碼只能對作用中儲存格起作用
我改寫如下可以批次產生但是擷取出來的數字像階梯般排列不知要怎麼修才可以齊頭式排列

Sub Main()
Dim str, sOneChar As String
Dim sNumber As String
Dim isNumber As Boolean
Dim nCount As Integer
Dim j As Integer
nCount = 0
isNumber = False
sNumber = ""
For j = 1 To 7
str = Cells(j, "B").Offset(0, -1)
For nI = 1 To Len(str)
sOneChar = Mid(str, nI, 1)
If isYourNumber(sOneChar) Then
sNumber = sNumber & sOneChar
isNumber = True
Else
If isNumber Then
Cells(j, "B").Offset(0, nCount).Value = stringToValue(sNumber)
nCount = nCount + 1
sNumber = ""
isNumber = False
End If
End If
Next nI
Next j
End Sub

Function isYourNumber(ByVal ch As String) As Boolean
isYourNumber = False
If ch = "0" Or ch = "1" Or ch = "2" Or ch = "3" Or ch = "4" Or ch = "5" Or ch = "6" Or ch = "7" Or ch = "8" Or ch = "9" Or ch = "," Or ch = "." Then
isYourNumber = True
End If
End Function

Function stringToValue(ByVal str As String) As Long
Dim s As String
s = Replace(str, ",", "")
stringToValue = Val(s)
End Function

海綿寶寶 iT邦大神 1 級 ‧ 2020-04-30 23:51:56 檢舉

把nCount=0搬個位置即可

Dim j As Integer
nCount = 0
isNumber = False
sNumber = ""
For j = 1 To 7
str = Cells(j, "B").Offset(0, -1)
For nI = 1 To Len(str)
sOneChar = Mid(str, nI, 1)

改成

Dim j As Integer
isNumber = False
sNumber = ""
For j = 1 To 7
nCount = 0
str = Cells(j, "B").Offset(0, -1)
For nI = 1 To Len(str)
sOneChar = Mid(str, nI, 1)

試試看

登入發表回應