范围是2000行,一共三种条件,判断B列数据中,每个单元格是否含有AAA字符,如果有就向左截取此单元格内10位字符写到A列同行中;如果没有AAA,再判断里面是否有AAS字符,如果有就从左截取9位字符,也写到A列同行中;如果这两个都不满足,就把该单元格内文字完整复制到A列同行中。
Sub aaa()
Dim c As Range, st%, sp%, wordLen%
With WorksheetFunction
For Each c In Range("B1:B2000").Cells
If .CountIf(c, "*AAA*") > 0 Then
sp = .Search("AAA", c)
st = .Max(1, sp - 10)
wordLen = sp - st
c.Offset(0, -1) = Mid(c, st, wordLen)
ElseIf .CountIf(c, "*AAS*") > 0 Then
sp = .Search("AAS", c)
st = .Max(1, sp - 9)
wordLen = sp - st
c.Offset(0, -1) = Mid(c, st, wordLen)
Else
c.Offset(0, -1) = c
End If
Next c
End With
End Sub
Dim c As Range, st%, sp%, wordLen%
With WorksheetFunction
For Each c In Range("B1:B2000").Cells
If .CountIf(c, "*AAA*") > 0 Then
sp = .Search("AAA", c)
st = .Max(1, sp - 10)
wordLen = sp - st
c.Offset(0, -1) = Mid(c, st, wordLen)
ElseIf .CountIf(c, "*AAS*") > 0 Then
sp = .Search("AAS", c)
st = .Max(1, sp - 9)
wordLen = sp - st
c.Offset(0, -1) = Mid(c, st, wordLen)
Else
c.Offset(0, -1) = c
End If
Next c
End With
End Sub
温馨提示:答案为网友推荐,仅供参考