时间戳

首页 > 解决方案 > 在字符串中查找子字符串，但从右到左查找excel

excel - 在字符串中查找子字符串，但从右到左查找excel

问题描述

我最初问了下面的问题，发现了一个很好的公式 excel 公式在文件路径文本字符串中查找零件号

因此，在使用=MID(A1,SEARCH("????-??????-??",A1),14)公式查找零件编号名称之后，一切似乎都很好，直到我遇到类似于下面示例集的数据。

c:\users\stuff\folder_name\folder_name_9876-vv-123-Ag\1234-000001-01_ baskets_1.pdf
c:\users\stuff\folder_name_9876-vv-123-Ag\random file_1234-000001-02_ baskets_2.pdf
c:\users\stuff\folder_name_XXXX-XXXXXX-XX\1234-000001-03_ baskets_3.pdf
c:\users\stuff\folder_name\1234-000030-01_ tree_30.pdf
c:\users\stuff\folder_name\random text_1234-000030-02_ tree_30.pdf
c:\users\stuff\folder_name\more random stuff_1234-000030-02_ tree_30.pdf

我正在查看几十万个文件名，我知道由于误报触发器，我丢失了一些重要文件。喜欢 9876-vv-123-Ag它在技术上符合????-??????-??格式，但不是我想从字符串中提取的内容。现在的问题是，有没有一种方法可以应用=MID(A1,SEARCH("????-??????-??",A1),14)，可以从字符串的右侧查看并向左工作以确保它抓取满足 ????-??????-??格式的完整字符串中的最后一个子字符串？

或者有没有办法将通配符搜索设置为仅数字而不是该????-??????-??格式的任何内容？

标签： excelsearchsubstringright-to-left

解决方案

如果没有其他歧义，您可以只搜索文件路径的文件名部分，并返回匹配的字符串：

=MID(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)),SEARCH("????-??????-??",TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))),14)

在此处输入图像描述

如果还有其他歧义，您将需要更具体

公式的这一部分： TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)) 返回字符串在“\”字符处拆分的最后一个子字符串。如果文件名可能超过99 个字符，则适当增加99，或使用类似的字符LEN(A1)。

如果要使用 VBA 例程，以下例程在 4 秒内处理了 500,000 行。它确实取决于仅由数字组成的零件编号，但如果需要包含其他模式，则更改将是微不足道的。

Option Explicit
Sub partNum()
Dim RE As Object
Dim R As Range, WS As Worksheet
Dim vSrc As Variant, vRes As Variant
Dim I As Long

Set WS = Worksheets("sheet1")
With WS
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    Set R = .Range(.Cells(1, 2), .Cells(UBound(vSrc, 1), 2))
End With

ReDim vRes(1 To UBound(vSrc, 1), 1 To 1)

Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "\d{4}-\d{6}-\d{2}"

    For I = 1 To UBound(vSrc)
        If .test(vSrc(I, 1)) = True Then vRes(I, 1) = .Execute(vSrc(I, 1))(0)
    Next I
End With

R.EntireColumn.Clear
R = vRes

End Sub

VBA 函数 （独立）

Option Explicit
Function getPartNum(S As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "\d{4}-\d{6}-\d{2}(?!.*\\)"
    If .test(S) = True Then getPartNum = .Execute(S)(0)
End With
End Function

推荐阅读