从具有不同字符长度的单元格中提取数字 [英] Extracting digits from a cell with varying char length

问题描述

我有一组单元格，字符串的第一个永不更改，它一直且将一直(直到编码器对其进行更改) 20 个字符(包含空格).

I have a group of cells, the first of the string never changes, it is and always will (until the coder changes it) 20 characters (inc spaces).

然后我想从其余序列中提取3个数字(在某些情况下为2个).

I then want to extract the 3 numbers (and in some cases 2) from the remaining sequence.

The monthly cost is 2 silver, 1 copper and 40 iron.
The monthly cost is 1 silver, 94 copper and 40 iron.
The monthly cost is 1 silver and 75 copper.
The monthly cost is 8 silver and 40 copper.
The monthly cost is 1 silver.
The monthly cost is 99 silver, 99 copper and 99 iron.
The monthly cost is 1 gold.

在上面的示例中，您可以看到前20个字符后没有设置值.

In the sample above you can see that there is no set value after the first 20 chars.

1 or 99 silver 
1 or 99 copper
0, 1 or 99 iron  

我无法获得使所有单元格都正确的序列，我尝试了以下操作:

I can't get a sequence that gets all the cells correct, I've tried the following:

=IF(J7<>1,(MID(TRIM(J7),FIND(" iron",TRIM(J7))-2,FIND(" iron",TRIM(J7))-FIND(" iron",TRIM(J7))+3)),"")    
results in:  #VALUE!  (when no iron)  

=TRIM(MID(J6,FIND(" silver",J6)-2,LEN(J6)-FIND(" silver",J6)-26))&TRIM(MID(J6,FIND(" copper",J6)-2,LEN(J6)-FIND(" copper",J6)-16))&TRIM(MID(J6,FIND(" iron",J6)-2,LEN(J6)-FIND(" iron",J6)-3))  
results in:  1 s9440   

=MID(J7,31,2-ISERR(MID(J7,21,1)+0))  
results in:  nd

如果我将这些单元格作为计算的一部分，则它们将不会在下一个数学步骤中进行计算，因为我必须在其中允许 spaces 我的代码，如果可能有2位数字，而不是单个数字.

If I & the cells as part of the calculation, they then don't calculate in the next mathematical step as I've had to allow for spaces in my code, in the case that there may be 2 digit numbers, not single.

=MID(J5,SEARCH(" silver",J5,1)-2,2)&MID(J5,SEARCH(" copper",J5,1)-2,2)&MID(J5,SEARCH(" iron",J5,1)-2,2)  
results:   2 140
not:       2140

我最后需要做的是:

2140  
19440  
175  
840  
1  
999999   

非常感谢.

推荐答案

涉及字符串中的模式匹配时，通常使用RegEx.

When it comes to pattern matching in strings, RegEx if often the way to go.

在Excel中，这需要VBA解决方案，使用对"Microsoft VBScript Regular Expresions 5.5"的引用(如果愿意，可以延迟绑定)

In Excel, this requires a VBA solution, using a reference to "Microsoft VBScript Regular Expresions 5.5" (you can go late bound if you prefer)

这是您的案例的入门者，作为UDF

Here's a starter for your case, as a UDF

将其用作类似于 = GetValues(A1)的公式，假设第一个原始数据位于 A1 中.向下复制所需的任意行

Use it as a formula like =GetValues(A1) assuming 1st raw data is in A1. Copy down for as many rows as required

这将从一个字符串中提取最多3个值.

This will extract up to 3 values from a string.

Function GetValues(r As Range) As Variant
    Dim re As RegExp
    Dim m As MatchCollection
    Dim v As Variant
    Dim i As Long
    Set re = New RegExp

    re.Pattern = "(\d+)\D+(\d+)\D+(\d+)"
    If re.test(r.Value) Then
        Set m = re.Execute(r.Value)
    Else
        re.Pattern = "(\d+)\D+(\d+)"
        If re.test(r.Value) Then
            Set m = re.Execute(r.Value)
        Else
            re.Pattern = "(\d+)"
            If re.test(r.Value) Then
                Set m = re.Execute(r.Value)
            End If
        End If
    End If
    If m Is Nothing Then
        GetValues = vbNullString
    Else
        For i = 0 To m.Item(0).SubMatches.Count - 1
            v = v & m.Item(0).SubMatches(i)
        Next
        GetValues = v
    End If
End Function

这篇关于从具有不同字符长度的单元格中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！