I understand a little better the deleted. So …
Sheet1 A2 = "A25G Volvo|A25F Volvo"
Before:
Sheet2 A2 = "860 Volvo BM|861 Volvo BM|5350 Volvo BM|5350B Volvo BM|5350B 4x4 Volvo BM|5350B 6x4 Volvo BM|A20 Volvo BM|A20 6x4 Volvo BM|A20C Volvo BM|A20C Volvo|A25 Volvo BM|A25 4x4 Volvo BM|A25 6x4 Volvo BM|A25B Volvo BM|A25B 4x4 Volvo BM|A25C Volvo BM|A25C Volvo|A25C 4x4 Volvo|A25C 4x4 Volvo BM|A25D Volvo|A25D 4x4 Volvo|A25E Volvo|A25E 4x4 Volvo|A25F Volvo SN 13001-99999|A25F Volvo SN 320001-|A25G Volvo|A30 Volvo BM|A30C Volvo|A30C Volvo BM|A30D Volvo|A30E Volvo|A30F Volvo SN 12001-99999"
After:
Sheet2 A2 = "A25G Volvo"
since "A25G Volvo" is the only piece in Sheet2 A2
the result in Sheet2 A2 : A25F Volvo |A25G Volvo
There is no exact match for "A25F Volvo" in Sheet2 A2, but there are these:
"A25F Volvo SN 13001-99999|A25F Volvo SN 320001-"
Matching SN's is difficult
I don't understand the "with different sort"
Do you want the pieces in each cell in Sheet2 sorted Low-High?
Try this which seems to do the exact matching and deletes
Option Explicit
Sub Something3()
Dim wsMain As Worksheet, wsClean As Worksheet
Dim aryMain As Variant, aryClean As Variant
Dim i As Long, j As Long
Dim aryPieces As Variant
Set wsMain = Worksheets("Sheet1")
aryMain = wsMain.Cells(1, 1).CurrentRegion.Value
Set wsClean = Worksheets("Sheet2")
aryClean = wsClean.Cells(1, 1).CurrentRegion.Value
For i = LBound(aryMain, 1) To UBound(aryMain, 1)
aryMain(i, 1) = aryMain(i, 1) & "|"
aryClean(i, 1) = aryClean(i, 1) & "|"
Next i
'In Sheet1 in A2 We have A25G Volvo|A25F Volvo and when run The VBA in Sheet2,
'the result in Sheet2 A2 : A25F Volvo |A25G Volvo
'Mean it must delete the value that does not exist in sheet 1,
'So in Sheet 2 we will see the same value that we have in sheet1 but with different sort .
For i = LBound(aryMain, 1) To UBound(aryMain, 1)
aryPieces = Split(aryClean(i, 1), "|")
For j = LBound(aryPieces) To UBound(aryPieces) - 1 ' get extra because added | above
If InStr(aryMain(i, 1), aryPieces(j)) = 0 Then aryPieces(j) = vbNullString
Next j
aryClean(i, 1) = Join(aryPieces, "|")
Do While InStr(aryClean(i, 1), "||") > 0
aryClean(i, 1) = Replace(aryClean(i, 1), "||", "|")
Loop
If Right(aryClean(i, 1), 1) = "|" Then
aryClean(i, 1) = Left(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
End If
If Left(aryClean(i, 1), 1) = "|" Then
aryClean(i, 1) = Right(aryClean(i, 1), Len(aryClean(i, 1)) - 1)
End If
Next i
wsClean.Cells(1, 1).CurrentRegion.Value = aryClean
End Sub