Sub snb()
sn = Cells(11, 1).CurrentRegion
For j = 2 To UBound(sn)
sq = Split(sn(j, 1))
For jj = 1 To 3
If Len(sq(jj)) = 2 Then
If Asc(Left(sq(jj), 1)) > 64 And Asc(Right(sq(jj), 1)) < 92 Then
If Val(sq(jj + 1)) > 9999 Or Len(sq(jj + 1)) + Len(sq(jj + 2)) = 6 Then
sq(jj) = "_" & sq(jj) & "_"
If Val(sq(jj + 1)) > 9999 Then
sq(jj + 1) = sq(jj + 1) & "_"
Else
sq(jj + 2) = sq(jj + 2) & "_"
End If
End If
End If
sn(j, 1) = Replace(Replace(Join(sq, " "), "_ ", "_"), " _", "_")
If InStr(sn(j, 1), "_") > 0 Then Exit For
End If
Next
Next
Cells(30, 1).Resize(UBound(sn)) = sn
Cells(31, 1).Resize(UBound(sn)).TextToColumns , 1, , , False, False, False, False, True, "_"
End Sub
Bookmarks