Option Explicit
Private Const sDeli1 As String = ","
Private Const sDeli2 As String = "|"
Private Const strJoin As String = ", "
'
Function getStringCode(ByVal str As String) As String
Const sMatch1 As String = " VN"
Const sMatch2 As String = " ng"
Dim index1 As Long, index2 As Long
Dim data1 As Variant, item1 As Variant, data2 As Variant, item2 As Variant
Dim result As Variant, i As Long, ii As Long, strFirstCode As String
index1 = InStr(1, str, sMatch1, vbBinaryCompare)
index2 = InStr(1, str, sMatch2, vbBinaryCompare)
If index1 > 0 And index2 > 0 Then
str = Mid(str, index1 + 1, index2 - index1 - 1)
str = cleanString(str)
data1 = Split(str, sDeli1)
ReDim result(1 To 10000)
For Each item1 In data1
data2 = Split(item1, sDeli2)
strFirstCode = data2(LBound(data2))
ii = ii + 1
result(ii) = strFirstCode
If UBound(data2) > LBound(data2) Then
For i = LBound(data2) + 1 To UBound(data2)
item2 = data2(i)
ii = ii + 1
result(ii) = VBA.Left$(strFirstCode, Len(strFirstCode) - Len(item2)) & item2
Next i
End If
Next item1
If ii > 0 Then
ReDim Preserve result(1 To ii)
getStringCode = Join(result, strJoin)
End If
End If
End Function
Private Function cleanString(ByVal str As String) As String
Dim listReplace As Variant, item As Variant
listReplace = Array("va", "&", "+")
For Each item In listReplace
str = Replace(str, item, " ")
Next item
str = WorksheetFunction.Trim(str)
str = Replace(str, strJoin, sDeli1)
str = Replace(str, " ", sDeli2)
cleanString = str
End Function
