求助如何将单元格内的月份提取出来

quqiyuan

禁忌の双极 发表于 2025-4-6 20:55
方便的话可以添加注释让我学习一下吗，万一改位置了我自己也可以自己改了

稍微有点基础都看出来吧，写注释比写代码花时间。其实就是数组，循环。

今铭昔

xxdddbw

禁忌の双极 发表于 2025-4-6 20:55
方便的话可以添加注释让我学习一下吗，万一改位置了我自己也可以自己改了

代码复制给deepseek，让它写注释。

zhangcheng6688

借楼学习一下正则
Sub test()
arr = [a1].CurrentRegion
Set reg = CreateObject("vbscript.regexp")
reg.Pattern = "(\d+)月份"
For i = 2 To UBound(arr)
   If reg.test(arr(i, 1)) Then
      Set mh = reg.Execute(arr(i, 1))
      arr(i, 2) = mh(0).submatches(0)
   End If
Next
[a1].CurrentRegion = arr
End Sub

usedrange

1. Sub tt()
   
2.     Dim s, i%, wb As Range
   
3.     With CreateObject("vbscript.regexp")
   
4.         .Pattern = "\d+(?=月)"
   
5.         Set wb = Sheet1.[a2].CurrentRegion
   
6.         For i = 2 To UBound(wb.Value)
   
7.             Set s = .Execute(wb(i, 1))
   
8.             If s.Count > 0 Then Cells(i, "d") = s(0).Value
   
9.         Next i
   
10.     End With
    
11.     Set wb = Nothing
    
12. End Sub

复制代码

henterwu

感觉可以用函数更灵活。=CatchYM(A1,"M")

Function CatchYM(inText As String, x As String) As String
    With CreateObject("vbscript.regexp")
      If x = "M" Then .Pattern = "\d+(?=月)"
      If x = "Y" Then .Pattern = "\d+(?=年)"
             Set s = .Execute(inText)
              If s.Count > 0 Then CatchYM = s(0).Value
    End With
End Function

awen7878

Sub ExtractMonth()
    Dim regEx As Object, dataArr As Variant, resultArr() As Variant
    Dim i As Long, lastRow As Long, ws As Worksheet
   
    Set ws = ThisWorkbook.Worksheets("Sheet1") '按实际修改工作表名
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    dataArr = ws.Range("A1:A" & lastRow).Value
    ReDim resultArr(1 To UBound(dataArr), 1 To 1)
   
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Pattern = "(\d{1,2})月份"
   
    For i = 1 To UBound(dataArr)
        If regEx.Test(dataArr(i, 1)) Then
            resultArr(i, 1) = regEx.Execute(dataArr(i, 1))(0).SubMatches(0)
        End If
    Next i
   
    ws.Range("D1:D" & lastRow).Value = resultArr
End Sub

禁忌の双极

usedrange 发表于 2025-4-7 09:07

非常感谢大佬相助

坐如松

D2=SUM(IFERROR(MID(TEXT(MID(SUBSTITUTE(A2&"座","月份",REPT(" ",99)),ROW($1:$999),99),),2,99)*1,""))
下拉

hcm19522

=IFERROR(-LOOKUP(1,-MID(A2,FIND("月",A2)-{1,2},{1,2})),"")