如何利用正则表达式生成随机文本填充Excel单元格以制作测试文件？

Absolutely! You can generate regex-compliant random strings for your Excel test files, though native Excel formulas don’t natively accept regex patterns directly. Given your Windows 7 64-bit setup, the most flexible solution is a custom VBA function, but there are formula-based workarounds for simpler cases too. Let’s dive in:

1. VBA Custom Function (Best for Complex Regex Patterns) #

Since you need to handle varying column structures and specific regex rules, a VBA function gives you full control over matching valid patterns. Here’s how to set it up:

Step 1: Open the VBA Editor #

• Press Alt + F11 in Excel to launch the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module to create a new code module.

Step 2: Paste the Custom Function #

Add this code to the module. It uses VBScript’s regex engine to generate and validate random strings until they match your pattern, with a safety limit to avoid infinite loops:

Function RandomRegexString(pattern As String, Optional maxLength As Integer = 20) As String
    Dim regex As Object
    Dim randomString As String
    Dim charPool As String
    Dim i As Integer
    Dim attempts As Integer
    Const MAX_ATTEMPTS As Integer = 1000 ' Prevent infinite loops for invalid patterns
    
    Set regex = CreateObject("VBScript.RegExp")
    regex.pattern = pattern
    regex.Global = True
    
    ' Customize this pool to match valid characters for your columns
    charPool = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&*()"
    
    attempts = 0
    Do
        attempts = attempts + 1
        randomString = ""
        ' Generate a random string of variable length (1 to maxLength)
        For i = 1 To Int((maxLength * Rnd) + 1)
            randomString = randomString & Mid(charPool, Int((Len(charPool) * Rnd) + 1), 1)
        Next i
    Loop Until regex.Test(randomString) Or attempts >= MAX_ATTEMPTS
    
    If attempts >= MAX_ATTEMPTS Then
        RandomRegexString = "⚠️ No valid string found (check pattern/maxLength)"
    Else
        RandomRegexString = randomString
    End If
End Function

Step 3: Use the Function in Excel #

In any cell, enter the function with your desired regex pattern. For example:
=RandomRegexString("[A-Z]{3}-\d{4}")
This generates a string like XYZ-1234, matching the pattern of 3 uppercase letters, a hyphen, and 4 digits.

Key Customizations:

• Character Pool: Modify the charPool variable to include only characters valid for each column (e.g., remove special symbols if your column only accepts alphanumeric values).
• Max Length: Adjust the optional maxLength parameter to fit your column’s string limits.

2. Formula-Based Approach (For Simple Patterns) #

If you prefer to avoid VBA, you can combine built-in Excel functions to mimic basic regex rules. This works best for straightforward patterns like fixed-length alphanumeric strings or valid dates:

Example 1: 8-Character Alphanumeric String (Mix of uppercase, lowercase, digits) #

For Excel 2013+:
=CONCAT(CHAR(RANDBETWEEN(65,90)), CHAR(RANDBETWEEN(97,122)), CHAR(RANDBETWEEN(48,57)), CHAR(RANDBETWEEN(65,122)), CHAR(RANDBETWEEN(48,57)), CHAR(RANDBETWEEN(65,90)), CHAR(RANDBETWEEN(97,122)), CHAR(RANDBETWEEN(48,57)))

For older Excel versions (2010 and earlier):
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(65,122))&CHAR(RANDBETWEEN(48,57))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(48,57))

Example 2: Valid Date in YYYY-MM-DD Format #

=TEXT(DATE(RANDBETWEEN(2020,2024), RANDBETWEEN(1,12), RANDBETWEEN(1,28)), "YYYY-MM-DD")

Limitation:

This method gets unwieldy for complex regex (like optional groups, variable lengths, or specific character sequences). For those cases, the VBA function is far more efficient.

3. Pro Tips for Your Test Data Workflow #

• Batch Fill: Once you have the function/formula set up, drag the fill handle down/across to populate entire columns with valid random data.
• Validation: For extra safety, add Excel data validation using the same regex pattern. For Excel 365/2021, use =REGEXMATCH(A1, "[your-pattern]"); for older versions, use the VBA function in a custom validation rule.
• Save Correctly: If using VBA, save your file as a macro-enabled workbook (.xlsm) to retain the custom function.

Content of the question originates from Stack Exchange, question author Antonycx