比如第一个名称中“GZAW2~GZAW1”,符号“~”左面的“GZAW2”就包含了4个字母,程序中有注释,以后的字母位数更改下就行了:
- Sub test()
- Dim k%, arr(1 To 10000, 1 To 2)
- Set fso = CreateObject("scripting.filesystemobject")
- Set sh = ActiveSheet
- Application.ScreenUpdating = False
- sh.Range("A2:B" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row + 1).ClearContents
- For Each f In fso.getfolder(ThisWorkbook.Path).Files
- If InStr(f.Name, ".txt") <> 0 Then
- k = k + 1
- arr(k, 1) = k: arr(k, 2) = Split(f.Name, "_")(1)
- End If
- Next f
- For i = 1 To k - 1
- For j = i + 1 To k
- s1 = Mid(arr(i, 2), 5, InStr(arr(i, 2), "~") - 5) '里面的“5”是符号“~”左面的字母位数+1(4+1)
- s2 = Mid(arr(j, 2), 5, InStr(arr(j, 2), "~") - 5)
- If s1 = s2 Then
- s1 = Right(Len(arr(i, 2)), Len(arr(i, 2)) - InStr(arr(i, 2), "~") - 4) '里面的“4”是符号“~”左面的字母位数(4)
- s2 = Right(Len(arr(j, 2)), Len(arr(j, 2)) - InStr(arr(j, 2), "~") - 4)
- End If
- If InStr(s1, "-") > 0 And InStr(s2, "-") > 0 Then
- s1 = Val(Replace(s1, "-", ""))
- s2 = Val(Replace(s2, "-", ""))
- ElseIf InStr(s1, "-") > 0 And InStr(s2, "-") = 0 Then
- If Left(s1, 1) = Left(s2, 1) And Val(s1) = Val(s2) Then
- s1 = Val(Replace(s1, "-", ""))
- s2 = Val(s2)
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- ElseIf InStr(s1, "-") = 0 And InStr(s2, "-") > 0 Then
- If Left(s1, 1) = Left(s2, 1) And Val(s1) = Val(s2) Then
- s2 = Val(Replace(s2, "-", ""))
- s1 = Val(s1)
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- Else
- s1 = Val(s1)
- s2 = Val(s2)
- End If
- If s1 > s2 Then
- temp = arr(i, 2): arr(i, 2) = arr(j, 2): arr(j, 2) = temp
- End If
- Next
- Next
- sh.[A2].Resize(k, 2) = arr
- Application.ScreenUpdating = True
- End Sub
复制代码
|