• پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

    آموزش استفاده از Regex جهت پیدا کردن و جایگزینی رشته ها در اکسل
    این پست شامل فایل دانلود می باشد مشاهده

    عملکرد Regex Replace در اکسل شما وجود ندارد؟ این آموزش نشان می دهد که چگونه سریع آن را به کتابهای خود اضافه کنید، بنابراین می توانید از عبارات معمولی برای جایگزینی رشته های متن در Excel استفاده کنید.

    وقتی صحبت از تغییر یک قطعه به اطلاعات دیگر می شود ، مایکروسافت اکسل تعدادی گزینه برای انتخاب از جمله ابزار Find and Replace و چند عملکرد جایگزین ارائه می دهد. چرا شخص می خواهد با regexes همه چیز را پیچیده کند؟ از آنجا که ویژگی های استاندارد Excel فقط می تواند یک رشته دقیق را که شما مشخص کرده اید پردازش کند. برای یافتن رشته ای که با الگو مطابقت داشته باشد و آن را با چیز دیگری جایگزین کنید ، عبارات منظم ضروری هستند .

     
    تابع جایگزینی Excel VBA Regex

    همانطور که به طور کلی شناخته شده است ، توابع داخلی Excel از عبارات معمولی پشتیبانی نمی کنند. برای اینکه بتوانید از regexes در فرمول های خود استفاده کنید ، باید عملکرد خود را ایجاد کنید. خوشبختانه ، شی RegExp قبلاً در VBA وجود دارد و ما از این شی در کد زیر استفاده می کنیم :

    Public Function RegExpReplace(text As String, pattern As String, text_replace As String, 
     Optional instance_num As Integer = 0, Optional match_case As Boolean = True) As String
        Dim text_result, text_find As String
        Dim matches_index, pos_start As Integer
        On Error GoTo ErrHandl
        text_result = text
        Set regex = CreateObject("VBScript.RegExp")
        regex.pattern = pattern
        regex.Global = True
        regex.MultiLine = True
        If True = match_case Then
            regex.ignorecase = False
        Else
            regex.ignorecase = True
        End If
        Set matches = regex.Execute(text)
        If 0 < matches.Count Then
            If (0 = instance_num) Then
                text_result = regex.Replace(text, text_replace)
            Else
                If instance_num <= matches.Count Then
                    pos_start = 1
                    For matches_index = 0 To instance_num - 2
                        pos_start = InStr(pos_start, text, matches.item(matches_index), vbBinaryCompare) + Len(matches.item(matches_index))
                    Next matches_index
                    text_find = matches.item(instance_num - 1)
                    text_result = Left(text, pos_start - 1) & Replace(text, text_find, text_replace, pos_start, 1, vbBinaryCompare)
                End If
            End If
        End If
        RegExpReplace = text_result
        Exit Function
    ErrHandl:
        RegExpReplace = CVErr(xlErrValue)
    End Function

     

     
    Tips:

     

    • If you have little experience with VBA, this guide will walk you through the process: How to insert VBA code in Excel.
    • After adding the code, remember to save your file as a macro-enabled workbook (.xlsm).

    RegExpReplace syntax

    The RegExpReplace function searches an input string for values that match a regular expression and replaces the found matches with the text you specify.

    The function accepts 5 arguments, but only the first three are required.

    RegExpReplace(text, pattern, replacement, [instance_num], [match_case])

    Where:

    • Text (required) - the text string to search in.
    • Pattern (required) - the regular expression to match.
    • Replacement (required) - the text to replace the matching substrings with.
    • Instance_num (optional) - a serial number indicating which instance to replace. If omitted, the function will replace all found matches (default).
    • Match_case (optional) - controls whether to match or ignore text case. If TRUE or omitted (default), the search is case-sensitive; if FALSE - case-insensitive.

    The function works in all versions of Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 and Excel 2010.

    Usage notes

    To ensure that your results will meet your expectations, let's take a closer look at the inner mechanics:

    1. By default, the function works in the Replace all mode. To substitute a specific occurrence, put a corresponding number in the instance_num argument.
    2. By default, the function is case-sensitive. For case-insensitive search, set the match_case argument to FALSE. Because of the VBA RegExp limitations, the classic case-insensitive pattern (?i) is not supported.
    3. When you supply a regex directly in a formula, remember to enclose it in double quotation marks.
    4. If a valid pattern is not found, the function will return the original string with no changes.
    5. If the regex is invalid, a #VALUE! error will occur.

    Excel Regex replace examples

    Assuming you've already inserted the RegExpReplace function in your workbook, let's get to more fascinating things - using regular expressions for advanced find and replace in Excel.

    Regex to replace string matching a pattern

    In the sample dataset below, supposing you want to hide some personal data such as social security numbers. Given that SSN is a nine-digit number in the format "000-00-0000", we are using the following regular expression to find it.

    Pattern: \d{3}-\d{2}-\d{4}

    For replacement, this string is used:

    Replacement text: XXX-XX-XXXX

    With the original string in A5, the complete formula takes this form:

    =RegExpReplace(A5, "\d{3}-\d{2}-\d{4}", "XXX-XX-XXXX")

    For convenience, you can enter the pattern and replacement text in separate cells and refer to those cells in your formula. If you plan to use the formula for multiple cells, remember to lock the cell addresses with the $ sign:

    =RegExpReplace(A5, $A$2, $B$2)
    پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

    Regex to replace number in a string

    To find any single digit from 0 to 9, use \d in your regular expression. To find specific digits, use an appropriate quantifier or construct a more sophisticated regex like shown in the below examples.

    Replace all numbers

    To replace absolutely all numbers in a string with some character or text, use the + quantifier, which says to search for numbers containing 1 or more digits.

    Pattern: \d+

    For example, to replace all numbers in cell A5 with an asterisk, use this formula:

    =RegExpReplace(A5, "\d+", "*")
    پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

    Replace amounts of money

    In the same dataset, suppose you wish to replace only the amounts of money and not all the numbers. To have it done, you search for the dollar sign followed by one or more digits \$\d+ - this part matches the dollar unit. After the main unit, there may or may not be a fractional unit. To match it, you look for zero or one period after which come from 0 to 2 digits \.?\d{0,2}. The word boundary \b in the end ensures that the matching value is not part of a bigger number.

    Pattern: \$\d+\.?\d{0,2}\b

    Serve this regular expression to our custom function and you'll get the follwoing result:

    =RegExpReplace(A5, "\$\d+\.?\d{0,2}\b", "*")
    پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

    Regex to find and replace all matches

    In classic regular expressions, there is the global search flag /g that forces a regex to find all possible matches in a string. In VBA, this flag is not supported. Instead, the VBA RegExp object provides the Global property that defines whether to search for all occurrences or only the first one. In the code of our function, the Global property is set to True, meaning the pattern should be tested against all possible matches in a string.

    So, what do you do to replace all occurrences matching a pattern? Nothing special. This behavior is implemented by default.

    Let's say you manage an Excel file with personal data. Some information is confidential, so before sharing this file with your colleagues, you want to replace sensitive info such as social security numbers (SSN) and individual taxpayer identification numbers (ITIN) with "CONF".

    Given that both SSN and ITIN have the same format, you can find them using the below regular expression.

    Pattern: \d{3}-\d{2}-\d{4}

    Since the 4th instance_num argument of our function defaults to TRUE, you can safely omit it:

    =RegExpReplace(A5, "\d{3}-\d{2}-\d{4}", "CONF")

    As the result, all substrings matching the regex are replaced with the specified text:
    پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

    Regex to replace a specific instance

    To replace just one occurrence matching a given pattern, define the corresponding number in the instance_num argument.

    In the above example, supposing you wish to replace only social security numbers. In all cells, SSN is listed first, so we are replacing the 1st instance:

    =RegExpReplace(A5, $A$2, $B$2, 1)

    Where $A$2 is the pattern and $B$2 is the replacement text.
    پیدا کردن و جایگزینی رشته ها در اکسل با استفاده از Regex

     

    نظرات ارسال شده ارسال نظر جدید
    برای تبادل نظر، می بایست در سایت وارد شوید

    ورود به سایت
تماس گفتگو سبد خرید بالا