Excel VBA：如何从字符串中提取数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8100125/
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 : How do I extract number from a string?
提问by js0823
I want to extract numbers from a string. The strings are written in each cell like this.
我想从字符串中提取数字。字符串像这样写在每个单元格中。
1, 1
1, 2
1, 3
Numbers are simply divided by commas.
数字只是用逗号分隔。
How do I extract numbers from them in Excel VBA?
如何在 Excel VBA 中从它们中提取数字？
Thank you very much.
非常感谢。
回答by Bruno 82
If I got your question right, you have "1, 1" in cell A1, "1, 2" in cell A2, "1, 3" in cell A3.
如果我答对了您的问题，您的单元格 A1 中为“1, 1”，单元格 A2 中为“1, 2”，单元格 A3 中为“1, 3”。
If you want respectively the numbers before comma in cells B1, B2 and B3 and the numbers after the comma in cells C1, C2 and C3 you can do the following:
如果您想要分别在单元格 B1、B2 和 B3 中逗号前的数字和单元格 C1、C2 和 C3 中逗号后的数字，您可以执行以下操作：
VBA SOLUTION:
VBA 解决方案：
Public Sub test()
'the variables' declaration has been corrected, check
'the post's comments below to find out which correction were made
Dim lngLeft as Long, lngRight as Long, lngCommaPos As Long
Dim intI As Integer
For intI = 1 To 3
lngCommaPos = InStr(1, Range("A" & intI).Value, ",")
lngLeft = Left(Range("A" & intI).Value, lngCommaPos - 1)
lngRight = Right(Range("A" & intI).Value, Len(Range("A" & intI).Value) - lngCommaPos - 1)
Range("B" & intI).Value = lngLeft
Range("C" & intI).Value = lngRight
Next intI
End Sub
NON VBA solution:
非 VBA 解决方案：
Insert the following formula in cell B1:
=VALUE(LEFT(A1,FIND(",", A1)-1))
在单元格 B1 中插入以下公式：
=VALUE(LEFT(A1,FIND(",", A1)-1))
Insert the following formula in cell C1:
=VALUE(RIGHT(A1,LEN(A1)-FIND(",", A1)-1))
在单元格 C1 中插入以下公式：
=VALUE(RIGHT(A1,LEN(A1)-FIND(",", A1)-1))
Copy cells B1 and C1 Paste into cells B2 to C3
复制单元格 B1 和 C1 粘贴到单元格 B2 到 C3
If you want to have strings in columns B and C (instead of having numbers) you can drop the VALUE function, and the formulas in cells B1 and C1 will be
=LEFT(A1,FIND(",",A1)-1)
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
如果你想在 B 和 C 列中有字符串（而不是有数字），你可以删除 VALUE 函数，单元格 B1 和 C1 中的公式将为
=LEFT(A1,FIND(",",A1)-1)
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
回答by aevanko
*Assumption is that the desired result is 11, 12, and 13.
*假设期望的结果是 11、12 和 13。
The below have been written as functions, but can easily be changed to sub routines instead. I didn't want to get into setting ranges and just concentrate on the function.
以下已编写为函数，但可以轻松更改为子例程。我不想进入设置范围而只想专注于功能。
If your data is just numbers seperated by commas and spaces, then just use replace:
如果您的数据只是由逗号和空格分隔的数字，那么只需使用替换：
Function ExtractNumber(ByVal text As String) As String
ExtractNumber = Replace(text, ", ", "")
End Function
If you would like a more sophisticated function that will extract all numbers regardless of whatever may else be in the string, here's my RegexExtract function. By default, I set it up so that it will comma-seperate all captures, but you can specify it as none:
如果您想要一个更复杂的函数来提取所有数字，而不管字符串中的其他内容，这里是我的 RegexExtract 函数。默认情况下，我将其设置为以逗号分隔所有捕获，但您可以将其指定为 none：
=RegexExtract(A1, "(\d)", "")
- (\d) means to capture any numbers 0-9
- 3rd parameter is how you'd like all captures to be seperated (if at all)
- (\d) 表示捕获任何数字 0-9
- 第三个参数是您希望如何分离所有捕获（如果有的话）
Here is the function:
这是函数：
Function RegexExtract(ByVal text As String, _
ByVal extract_what As String, _
Optional seperator As String = ", ") As String
Application.ScreenUpdating = False
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String
RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)
For i = 0 To allMatches.Count - 1
For j = 0 To allMatches.Item(j).submatches.Count - 1
result = result & (seperator & allMatches.Item(i).submatches.Item(j))
Next
Next
If Len(result) <> 0 Then
result = Right$(result, Len(result) - Len(seperator))
End If
RegexExtract = result
Application.ScreenUpdating = True
End Function
回答by LeasMaps
I f you don't want to use VBA you could also use Text to Columns, see: http://support.microsoft.com/kb/214261
如果您不想使用 VBA，您也可以使用 Text to Columns，请参阅：http: //support.microsoft.com/kb/214261
