在Excel VBA中解析字符串而不留下尾随空格？

Question

我目前正在开发一个Excel电子表格，该电子表格能够从雅虎金融API导出数据，用于动态股票报价分析。我在将这些值正确解析到我的excel电子表格时遇到了问题。问题是最后一列数值有一个尾随的空格字符，这会阻止Excel将其识别为数字并将其格式化为逗号样式。

下面是我目前使用的函数：

Function UpdateStockData(rawWebpageData As String)
    Dim stockQuotes As Variant
    Dim stockQuoteValues As Variant

    Dim i As Integer
    Dim j As Integer

    stockQuotes = Split(rawWebpageData, vbLf)

    For i = 0 To UBound(stockQuotes)
        If InStr(stockQuotes(i), ",") > 0 Then
            stockQuoteValues = Split(stockQuotes(i), ",")
            For j = 0 To UBound(stockQuoteValues)
                sheet.Cells(5 + i, 4 + j).Value = stockQuoteValues(j)
                sheet.Cells(5 + i, 4 + j).Value = Trim(sheet.Cells(5 + i, 4 + j).Value)
            Next j
        End If
    Next i
End Function

以下是一些示例数据：

43.99,44.375,41.97,42.62,30098498
573.37,577.11,568.01,573.64,1871694
16.03,16.14,15.93,16.17,25659400
128.54,129.56,128.32,129.36,31666340
126.32,126.68,125.68,126.27,1629499
105.57,106.00,104.78,106.35,4972937
82.58,83.21,82.20,83.37,6214421
27.89,27.9173,27.62,27.83,1003967
49.07,49.56,48.92,49.55,13870589
43.055,43.21,42.88,43.28,25748692
34.12,34.41,33.72,34.095,23005798
159.42,160.56,158.72,161.03,3633635
43.01,43.90,41.00,40.30,10075067
100.25,100.48,99.18,99.74,9179359
139.54,140.49,138.75,140.69,1311226
119.86,120.05,118.7828,120.20,2931459
42.50,42.98,42.47,42.95,16262994
78.02,78.99,77.66,78.99,1826464
89.87,91.35,89.86,91.02,1773576
15.84,15.98,15.76,15.99,78441600
69.50,70.2302,69.49,70.49,2343967
80.895,81.15,78.85,79.60,28126686
33.08,33.20,32.955,33.25,739726
83.08,83.80,82.34,83.16,4475302
64.72,64.90,64.27,64.27,5147320
35.64,41.85,35.40,40.78,15871339
83.08,83.80,82.34,83.16,4475302
22.93,23.099,22.71,23.10,5290225
18.47,19.00,18.30,18.98,71891
69.65,69.684,69.08,69.98,5992137
154.35,155.22,154.00,155.57,4476188
80.08,81.16,79.77,81.51,7731275
47.79,48.87,47.31,48.58,2219634
23.04,23.21,22.97,23.23,891504
114.76,115.47,114.25,116.07,3799034
80.63,81.56,80.56,81.91,6140957
25.66,25.77,25.47,25.86,31543764
87.18,87.96,86.93,87.62,13467554
58.31,58.795,57.61,58.255,5791024
174.62,175.78,174.41,176.15,1035588
84.35,85.24,84.21,85.16,7369986
42.03,42.25,41.69,41.98,3192667
34.19,34.49,34.01,34.57,15652895
101.65,102.12,101.17,102.34,8665474
7.88,8.01,7.84,7.88,10425638
62.13,62.17,61.3525,61.97,16626413
23.10,23.215,22.85,23.18,651929

上面每行数据的最后一个值就是问题发生的地方。

Answer 1

检查最后一次迭代中最后一个字符的值，它可能是一个返回字符。您可以使用left函数获取或替换您想要的内容。

如果我们能看到rawWebpageData变量的值，答案就容易多了。

检查单元格格式，如果是文本，可以尝试将其设置为数字。如果我这样做了，我会调试数据，并逐步通过它来查找我没有检查的字符。