VBA中替换日期字符串首词并调整格式的问题求助

你的需求完全可行，原VBA代码失效的核心原因有两个：

1. 每次调用LCase()将单元格内容转为全小写后，替换目标仍用首字母大写的英文星期（如"Monday"），导致匹配失败，替换操作完全无效；
2. 代码未处理日期格式调整，无法将September 9, 2024转为9 September 2024的形式。

以下提供两种可靠解决方案：

方案一：文本替换法（适用于纯文本格式的日期） #

直接针对文本内容进行星期替换和日期格式重构：

Sub ConvertToIndonesianDate_Text()
    Dim iz As Integer
    Dim lsw As Long
    Dim cellText As String
    Dim parts() As String
    Dim datePart As String
    Dim dateParts() As String
    
    lsw = ThisWorkbook.Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    
    For iz = 2 To lsw
        cellText = Range("G" & iz).Value
        
        ' 替换英文星期为印尼语
        cellText = Replace(cellText, "Monday", "Senin")
        cellText = Replace(cellText, "Tuesday", "Selasa")
        cellText = Replace(cellText, "Wednesday", "Rabu")
        cellText = Replace(cellText, "Thursday", "Kamis")
        cellText = Replace(cellText, "Friday", "Jumat")
        cellText = Replace(cellText, "Saturday", "Sabtu")
        cellText = Replace(cellText, "Sunday", "Minggu")
        
        ' 重构日期格式：从"September 9, 2024"转为"9 September 2024"
        parts = Split(cellText, ", ")
        If UBound(parts) >= 1 Then
            datePart = parts(1)
            dateParts = Split(datePart, " ")
            If UBound(dateParts) >= 2 Then
                datePart = dateParts(1) & " " & dateParts(0) & " " & Replace(dateParts(2), ",", "")
                cellText = parts(0) & ", " & datePart
            End If
        End If
        
        Range("G" & iz).Value = cellText
    Next iz
End Sub

方案二：日期类型转换法（更可靠，兼容日期格式/可识别文本） #

先将内容转为日期类型，再通过映射和格式化生成目标格式，避免文本拆分的潜在错误：

Sub ConvertToIndonesianDate_DateType()
    Dim iz As Integer
    Dim lsw As Long
    Dim cellValue As Variant
    Dim indonesianWeekday As String
    
    ' 构建英文星期到印尼语的映射表
    Dim weekdayMap As Object
    Set weekdayMap = CreateObject("Scripting.Dictionary")
    weekdayMap("Monday") = "Senin"
    weekdayMap("Tuesday") = "Selasa"
    weekdayMap("Wednesday") = "Rabu"
    weekdayMap("Thursday") = "Kamis"
    weekdayMap("Friday") = "Jumat"
    weekdayMap("Saturday") = "Sabtu"
    weekdayMap("Sunday") = "Minggu"
    
    lsw = ThisWorkbook.Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row
    
    For iz = 2 To lsw
        cellValue = Range("G" & iz).Value
        
        ' 优先尝试转换为日期类型处理
        If IsDate(cellValue) Then
            indonesianWeekday = weekdayMap(Format(cellValue, "dddd"))
            Dim dateFormatted As String
            dateFormatted = Day(cellValue) & " " & Format(cellValue, "mmmm") & " " & Year(cellValue)
            Range("G" & iz).Value = indonesianWeekday & ", " & dateFormatted
        Else
            ' 无法识别为日期时，回退到文本替换逻辑
            cellValue = Replace(cellValue, "Monday", "Senin")
            cellValue = Replace(cellValue, "Tuesday", "Selasa")
            cellValue = Replace(cellValue, "Wednesday", "Rabu")
            cellValue = Replace(cellValue, "Thursday", "Kamis")
            cellValue = Replace(cellValue, "Friday", "Jumat")
            cellValue = Replace(cellValue, "Saturday", "Sabtu")
            cellValue = Replace(cellValue, "Sunday", "Minggu")
            
            parts = Split(cellValue, ", ")
            If UBound(parts) >= 1 Then
                datePart = parts(1)
                dateParts = Split(datePart, " ")
                If UBound(dateParts) >= 2 Then
                    datePart = dateParts(1) & " " & dateParts(0) & " " & Replace(dateParts(2), ",", "")
                    cellValue = parts(0) & ", " & datePart
                End If
            End If
            Range("G" & iz).Value = cellValue
        End If
    Next iz
End Sub

内容的提问来源于stack exchange，提问作者little turtle