Excel VBA批量替换单元格内ISO格式日期为指定格式求助

Got it, let's tackle this problem step by step. I know dealing with multiple dates in a single cell while preserving line breaks can be tricky—here's a solid VBA solution that should fix both the formatting and line break issues:

This script will scan your target cells, find all dates in YYYY/MM/DD format, convert them to DD MMMMMMMM YYYY (e.g., 1 January 2018), and keep all original line breaks and non-date text intact.

Sub ConvertMultipleDatesInCells()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim regEx As Object
    Dim matches As Object
    Dim match As Object
    Dim convertedText As String
    Dim originalText As String
    
    ' 👇 Adjust this to your target worksheet and range (e.g., Range("B2:B500"))
    Set ws = ThisWorkbook.ActiveSheet
    Set rng = ws.Range("A1:A100")
    
    ' Initialize regex to match YYYY/MM/DD dates
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        .Pattern = "\b(\d{4})/(\d{2})/(\d{2})\b" ' Matches exact YYYY/MM/DD pattern
        .Global = True ' Find all matches in the cell, not just the first
        .IgnoreCase = False
    End With
    
    ' Loop through each cell in the target range
    For Each cell In rng
        If cell.Value <> "" Then
            originalText = cell.Value
            convertedText = originalText
            
            ' Grab all date matches in the cell
            Set matches = regEx.Execute(originalText)
            
            ' Convert each matched date one by one
            For Each match In matches
                Dim convertedDate As Date
                ' Parse the regex groups into a valid date
                convertedDate = DateSerial( _
                    CInt(match.SubMatches(0)), ' Year
                    CInt(match.SubMatches(1)), ' Month
                    CInt(match.SubMatches(2))  ' Day
                )
                ' Replace the original date string with the new formatted version
                convertedText = Replace(convertedText, match.Value, Format(convertedDate, "dd mmmm yyyy"))
            Next match
            
            ' Put the converted text back, preserving line breaks
            cell.Value = convertedText
            ' Optional: Ensure wrap text is enabled to see line breaks clearly
            cell.WrapText = True
        End If
    Next cell
    
    ' Clean up objects
    Set regEx = Nothing
    Set matches = Nothing
    Set ws = Nothing
    Set rng = Nothing
    
    MsgBox "Date conversion completed successfully!", vbInformation
End Sub

How to Use This Script: #

1. Open your Excel file and press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer → Insert → Module.
3. Paste the code above into the new module.
4. Adjust the Set rng = ws.Range("A1:A100") line to match the actual range of cells you need to process.
5. Press F5 to run the macro, or go back to Excel, open the Developer tab, click Macros, select ConvertMultipleDatesInCells, and hit Run.

Key Notes: #

• This script preserves all original line breaks, spaces, and non-date text—only the YYYY/MM/DD dates are modified.
• If you need month names in a different language, tweak the Format function (e.g., for French, use Format(convertedDate, "dd mmmm yyyy", , vbFrench)).
• If your dates use hyphens instead of slashes (e.g., YYYY-MM-DD), just update the regex pattern to \b(\d{4})-(\d{2})-(\d{2})\b.

内容的提问来源于stack exchange，提问作者Kaneki Byte