استخراج یک الگو از درون متن

Question

تام یک کاربرگ دارد که حاوی حدود 20000 سلول پر از داده های متنی است. او باید از درون این سلول ها الگوی خاصی از متن را استخراج کند. الگوی ##-##### است که هر # یک رقم است. این الگو در یک مکان مشخص در هر سلول ظاهر نمی شود. تام به این فکر می کند که آیا راهی برای استخراج اطلاعات مورد نظر وجود دارد؟

راه های مختلفی وجود دارد که می توانید به این مشکل نزدیک شوید و راه حل صحیح برای نیازهای شما به ویژگی های داده هایی که با آنها کار می کنید بستگی دارد. اگر می دانید که تنها جایی در داده هایتان که خط تیره دارید، در الگوی شماست، می توانید با استفاده از فرمولی مانند زیر، وجود خط تیره را قطع کنید:

=MID(A1,FIND("-",A1)-2,8)

این خط تیره را پیدا می کند و سپس دو کاراکتر سمت چپ خط تیره را می گیرد. این بدیهی است که اگر در جاهای دیگر متن خط تیره وجود داشته باشد یا اگر ممکن است "الگوهایی" شامل اعداد غیر رقمی (مانند 12-34B32) وجود داشته باشد، کار نمی کند و شما می خواهید آنها را حذف کنید. در این صورت به فرمول بسیار پیچیده تری نیاز دارید:

=IF(ISERROR(INT(MID(A1, FIND("-", A1, 1)-2, 2)) & INT(MID(
A1, FIND("-", A1, 1)+1, 5))), "", MID(A1, FIND("-", A1)-2, 8))

این شامل یک مؤلفه بررسی خطا است که متوجه می شود آیا نویسه های درست قبل از خط تیره و درست بعد از خط تیره حاوی چیزی غیر از رقم هستند یا خیر. اگر این کار را انجام دهند، هیچ چیز برگردانده نمی شود.

تنها کاری که این رویکردهای فرمولی انجام نمی دهند، رسیدگی به موقعیت هایی است که ممکن است بیش از یک مورد از الگو در یک سلول وجود داشته باشد. در این صورت، ماکرو بهترین رویکرد است. موارد زیر الگوهای معتبر را استخراج کرده و در یک کاربرگ جدید به نام «نتایج» قرار خواهند داد.

Sub ExtractPattern()
    On Error Resume Next
    Set SourceSheet = ActiveSheet
    Set TargetSheet = ActiveWorkbook.Sheets("Results")
    If Err = 0 Then
        Worksheets("Results").Delete
    End If
    Worksheets.Add
    ActiveSheet.Name = "Results"
    Set TargetSheet = ActiveSheet
    Cells(1, 1).Value = "Found Codes"
    Cells(1, 1).Font.Bold = True
    iTargetRow = 2

    SourceSheet.Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
    Range(Selection, Cells(1)).Select

    For Each c In Selection.Cells
        If c.Value Like "*##-#####*" Then
            sRaw = c.Value
            iPos = InStr(sRaw, "-")
            Do While iPos > 0
                If iPos < 3 Then
                    sRaw = "  " & sRaw
                    iPos = iPos + 2
                End If
                sTemp = Mid(sRaw, iPos - 2, 8)
                sRaw = Mid(sRaw, iPos + 6, Len(sRaw))
                If sTemp Like "##-#####" Then
                    TargetSheet.Cells(iTargetRow, 1) = sTemp
                    iTargetRow = iTargetRow + 1
                Else
                    sRaw = Mid(sTemp, 4, 5) & sRaw
                End If
                iPos = InStr(sRaw, "-")
            Loop
        End If
    Next c
End Sub

توجه داشته باشید که ماکرو در دو جا از تابع Like استفاده می کند. نمونه اول تعیین می کند که آیا الگو در جایی از سلول رخ می دهد یا خیر، و نمونه دوم برای تعیین اینکه آیا کاراکترهای استخراج شده دقیقاً با الگوی مورد نظر مطابقت دارند یا خیر استفاده می شود.