Excel VBA - 提取字符串中的数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40365573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Extract numeric value in string
提问by MDMFD87
I have multiple tables of data in which, in one column, I have information about the value of various contracts. For each contract, the information contained in each cell of that column is presented as follows:
我有多个数据表，其中的一列包含有关各种合同价值的信息。对于每份合同，该列的每个单元格中包含的信息如下所示：
"The value of the contract is $XX,XXX.XX."
“合同的价值是$XX,XXX.XX。”
Sometimes, there will be additional text after the dollar value, as follows:
有时，在美元值后面会有额外的文字，如下：
"The value of the contract is $XX,XXX.XX. There is an option to modify the duration of the contract"
“合约价值为$XX,XXX.XX。有修改合约期限的选项”
I need to program a sub that allows me to extract the dollar value within that string of text and only keep that information (and none of the text coming before and after).
我需要编写一个子程序，它允许我提取该文本字符串中的美元值，并只保留该信息（并且没有前后文本）。
The difficulty I'm facing here is that everything is subject to change. The dollar value is never the same and the text before or after it also changes.
我在这里面临的困难是一切都可能发生变化。美元价值永远不会相同，它之前或之后的文本也会发生变化。
So far I've been able to successfully keep everything after the $ sign, using the SPLIT function and $ as delimiter. However, I keep having problems removing whatever text may follow the dollar value.
到目前为止，我已经能够成功地保留 $ 符号之后的所有内容，使用 SPLIT 函数和 $ 作为分隔符。但是，我一直在删除可能跟随美元价值的任何文本时遇到问题。
Any idea on how I could proceed?
关于我如何进行的任何想法？
Thank you for your help!
感谢您的帮助！
回答by John Coleman
The VBA function val()has the nice property that it isn't bothered by text which comes after the number. So something like the following is possible:
VBA 函数val()有一个很好的特性，它不会被数字后面的文本所困扰。所以类似下面的事情是可能的：
Function ExtractAmount(data As String) As Variant
Dim s As String
s = Split(data, "$")(1) 'part after first dollar sign
s = Replace(s, ",", "") 'throw away any commas
ExtractAmount = CCur(Val(s))
End Function
For example,
例如，
回答by Comintern
The easiest way to do this is with a regular expression:
最简单的方法是使用正则表达式：
'Requires a reference to Microsoft VBScript Regular Expressions X.X
Public Function ExtractNumber(inValue As String) As Double
With New RegExp
.Pattern = "(\d{1,3},?)+(\.\d{2})?"
.Global = True
If .Test(inValue) Then
ExtractNumber = CDbl(.Execute(inValue)(0))
End If
End With
End Function
Sample usage:
示例用法：
Sub Example()
Debug.Print ExtractNumber("The value of the contract is ,345.67. More text.")
End Sub
回答by maxhob17
Just in case its easier - you might not actually need a sub. A formula such as this:
以防万一它更容易 - 您实际上可能不需要潜艇。像这样的公式：
=VALUE(LEFT(MID(B3,FIND("$",B3)+1,LEN(B3)),FIND(".",B3)-FIND("$",B3)+2))
=VALUE(LEFT(MID(B3,FIND("$",B3)+1,LEN(B3)),FIND(".",B3)-FIND("$",B3)+2))
works for this example:
适用于这个例子：
回答by Jordan
Provided there are no numbers within the string other than the dollar value, this will work:
如果字符串中除了美元值之外没有数字，这将起作用：
Code
代码
Sub testingsub()
Dim str As String
Dim x, p1, p2 As Integer
str = "The value of the contract is Function GetMoney(txt As String) As String
GetMoney = "$" & Split(Split(txt, "$")(1), " ")(0)
End Function
,000.00. There is an option to modify the duration of the contract"
p1 = InStr(str, "$")
For x = Len(str) To 1 Step -1
If IsNumeric(Mid(str, x, 1)) Then
p2 = x + 1
Exit For
End If
Next x
Debug.Print Mid(str, p1, p2 - p1)
End Sub
Result
结果
$00,000.00
00,000.00 美元
回答by user3598756
if you don't bother last period
如果你不打扰上一期
Function GetMoney(txt As String) As String
GetMoney = "$" & Split(Split(txt, "$")(1), " ")(0)
GetMoney = Left(GetMoney, Len(GetMoney) - 1)
End Function
else
别的
##代码##