Excel VBA：如何从字符串中提取数字？

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.

非常感谢你。

3 个解决方案

#1

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（右（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） - 查找（ “”，A1）-1）

#2

*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:

如果您的数据只是用逗号和空格分隔的数字，那么只需使用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
（\ d）表示捕获任何数字0-9
3rd parameter is how you'd like all captures to be seperated (if at all)
第三个参数是你如何将所有捕获分开（如果有的话）

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

#3

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

#1