Sub GPE()
Dim sArr(), Res(), S, T
sArr = Sheets("ProducInOutStock").Range("C11", Range("C65000").End(xlUp)).Value
ReDim Res(1 To UBound(sArr), 1 To 12)
For i = 1 To UBound(sArr)
If Len(sArr(i, 1)) > 10 Then
If InStr(1, sArr(i, 1), "IPS") > 0 Then Res(i, 9) = "IPS"
If InStr(1, sArr(i, 1), "DF") > 0 Then Res(i, 10) = "DF"
If InStr(1, sArr(i, 1), "CAM") > 0 Then Res(i, 11) = "CAM"
S = Split(sArr(i, 1), "/")
T = Split(S(0), " ")
Res(i, 1) = T(0): Res(i, 2) = T(1)
Res(i, 3) = LoaiSo(S(1))
Res(i, 4) = Application.Trim(Split(S(2), "-")(1))
Res(i, 5) = S(3)
Res(i, 6) = Replace((S(4)), "-", "")
tmp = Replace(Replace((S(5)), "-", ""), " ", "")
If IsNumeric(tmp) Then
Res(i, 6) = Res(i, 6) & "-" & tmp
Else
If Len(S(5)) > 4 Then Res(i, 7) = S(5)
End If
If Left(Res(i, 6), 2) = "HD" Then
If InStr(1, Res(i, 6), "-") > 0 Then
Res(i, 7) = Split(Res(i, 6), "-")(0)
Res(i, 6) = Split(Res(i, 6), "-")(1)
Else
tmp = Res(i, 6)
Res(i, 6) = Res(i, 7)
Res(i, 7) = tmp
End If
End If
If Len(S(6)) > 4 Then Res(i, 7) = S(6)
For j = 1 To UBound(S)
If Len(S(j)) < 5 And InStr(1, S(j), "HD") > 0 Then
Res(i, 8) = S(j)
If UBound(S) - j = 2 Then
Res(i, 12) = Replace(S(j + 2), "CAM", "")
Else
Res(i, 12) = Split(S(j + 3), " ")(0)
If Res(i, 12) = "CAM" Then Res(i, 12) = Split(S(j + 4), " ")(0)
Res(i, 12) = Split(Res(i, 12), "-")(0)
If Res(i, 12) = "CAM" Then
a = 1
End If
End If
End If
Next j
End If
Next i
Sheets("ProducInOutStock").Range("R11").Resize(UBound(sArr), 12) = Res
End Sub
Private Function LoaiSo(ByVal iStr As String) As String
Dim n As Integer, j As Integer, tmp As String
n = Len(iStr)
For j = 1 To n
If Not IsNumeric(Mid(iStr, j, 1)) Then tmp = tmp & Mid(iStr, j, 1)
Next j
LoaiSo = tmp
End Function
