PDA

View Full Version : [SOLVED] Delete after coma when spicify workd does not exists



parscon
08-15-2018, 04:56 AM
Hello I have about 12000 row like these data and i need a VBA to remove the data between comma that the Volvo word does not exist mean






A30D Volvo, A30E Volvo, A30F Volvo, A30F Volvo, A35D Volvo, A40D Volvo, 11038734, 11145211, 17236991
A30D Volvo, A30E Volvo, A30F Volvo, A30F Volvo, A35D Volvo, A40D Volvo, 11038806, 17218651
A30D Volvo, A35D Volvo, A40D Volvo, 11036695







Will be like This :



A30D Volvo, A30E Volvo, A30F Volvo, A30F Volvo, A35D Volvo, A40D Volvo
A30D Volvo, A30E Volvo, A30F Volvo, A30F Volvo, A35D Volvo, A40D Volvo
A30D Volvo, A35D Volvo, A40D Volvo



Please help me if you can

YasserKhalil
08-15-2018, 05:15 AM
Try this code


Sub Test()
Dim a, x, s As String, i As Long, j As Long

a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

For i = LBound(a) To UBound(a)
x = Split(a(i, 1), ", "): s = ""
For j = LBound(x) To UBound(x)
If InStr(x(j), "Volvo") > 0 Then
s = s & IIf(s = "", "", ", ") & x(j)
End If
Next j
a(i, 1) = s
Next i

Range("B1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

snb
08-15-2018, 05:44 AM
I couldn't even wake up after my coma....

parscon
08-15-2018, 06:20 AM
Really appreciate for your help ! working great !

YasserKhalil
08-15-2018, 06:36 AM
You're welcome. Glad I can offer some help

jolivanes
08-16-2018, 10:18 PM
@ snb
Were you sleep deprivied