Remove specific contains

[Jasa P]

Hi guys, thank you for passing by. Would you like to help me please?
I have data as shown below on Excel, I need a macro that can remove the red contains.



ROW 1
sms,"READ,RECEIVED","+6281363105225 (tel:%2B6281363105225)","","","2013.10.09 14:25","","B06=14. 15. 16. 216 x12.. /324X2.2/. 306X2.2./ 390X2.10/. 725X3.3/. 24X6./ 97.X12/. 44. 10.X6./ 912X.2.3. /982X2.2/. 904X2.2./ .34X6/. 43X6./30.03.32, 23x.20./"

will be replaced with B06=14. 15. 16. 216 x12.. /324X2.2/. 306X2.2./ 390X2.10/. 725X3.3/. 24X6./ 97.X12/. 44. 10.X6./ 912X.2.3. /982X2.2/. 904X2.2./ .34X6/. 43X6./30.03.32, 23x.20./

ROW 2
sms,"READ,RECEIVED","+6281363105225","","","2013.10.09 14:15","","A05=632.668.431.467x2x5./ 1279.1211x2x2x2./24.23.146.56.46.135.35x5./0249.x2.2.4./48.48.x2./62.26.05.50 (http://62.26.5.50/). 96.69.35.53.09. 90,46.64.x8./57.75.68.86x20./"

will be replaced with A05=632.668.431.467x2x5./ 1279.1211x2x2x2./24.23.146.56.46.135.35x5./0249.x2.2.4./48.48.x2./62.26.05.50 (http://62.26.5.50/). 96.69.35.53.09. 90,46.64.x8./57.75.68.86x20./

ROW 3
sms,"READ,RECEIVED","+6281363105225 (tel:%2B6281363105225)","","","2013.10.09 13:51","","G04=03x9./.71x6/..39x3./.75x3./.31x3./.667x3./.67x3./.52x12../2958.x5.5.15./85x15./79, 28.14x.3./82,79, 59.95.x4./41x5./ 46x.6./64x8./ 258.285.958.985x.2./"

will be replaced with G04=03x9./.71x6/..39x3./.75x3./.31x3./.667x3./.67x3./.52x12../2958.x5.5.15./85x15./79, 28.14x.3./82,79, 59.95.x4./41x5./ 46x.6./64x8./ 258.285.958.985x.2./

ROW 4
sms,"READ,RECEIVED","+6281363105225","","","2013.10.09 13:46","","B03=9703x2. 10. 5./ 98.89.96.69. 36.63X6./ 98X5./1842X.2.3. 15./2019X.3.3.6./91X6./04.40.76X 4./91X7./92X6./93X.3./05,64.46x.6./785.85.10.17. 43x.3./92x7./91. 42x5./"

ROW 5
sms,"READ,RECEIVED","+6281363105225","","","2013.10.09 13:39","","D02=6210.8910.X 3./210,910X5./.60,10.X10./6982, 6382.X3./382,982,82X.6./428.482x.3./98x30./59.95.98.30x10./902. 906x10,10./08 x10./70x25./24x10./16.60x5./96,624x4./1624X2./"

will be replaced with D02=6210.8910.X 3./210,910X5./.60,10.X10./6982, 6382.X3./382,982,82X.6./428.482x.3./98x30./59.95.98.30x10./902. 906x10,10./08 x10./70x25./24x10./16.60x5./96,624x4./1624X2./

ROW 6
sms,"READ,RECEIVED","+6281363105225","","","2013.10.09 13:34","","B01=70X20./186X.6.6./08,80X.2./155, 190.X2./54.52.19 .X3./55.90.91.18 (http://55.90.91.18/). 10.X.4./4577,5477.477.577.x3./ 77x.20./345.34.x2./45.54x.4./428.482x2./82.28x15./25.52.57.75x.4./"

will be replaced with B01=70X20./186X.6.6./08,80X.2./155, 190.X2./54.52.19 .X3./55.90.91.18 (http://55.90.91.18/). 10.X.4./4577,5477.477.577.x3./ 77x.20./345.34.x2./45.54x.4./428.482x2./82.28x15./25.52.57.75x.4./



and so on... (the amount of row is unlimited)
I can't make sure there will be only 5 or 6 rows..
It can be at any amount.





Thank you so much guys,
Hope to hear something from you guys :)

[ZVI]

Hi,
Assuming those rows are in A-column, try this:


Sub Test()
Const QT = """"
Dim a(), i As Long, r As Long, v As String
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
If .Count = 1 Then
ReDim a(1 To 1, 1 To 1)
a(1, 1) = .Value
Else
a() = .Value
End If
For r = 1 To UBound(a)
v = Trim(a(r, 1))
If Len(v) Then
If Right(v, 1) = QT Then v = Left(v, Len(v) - 1)
i = InStrRev(v, QT)
If i Then v = Mid(v, i + 1)
a(r, 1) = Trim(v)
End If
Next
.Value = a()
End With
End Sub

[shrivallabha]

If you are interested in a formula based approach also then in Cell B1 Array Enter following formula [CTRL+SHIFT+ENTER]:
=SUBSTITUTE(MID(A1,MAX(IFERROR(FIND(""",""",A1,ROW($A$1:INDEX(A:A,LEN(A1)))),0))+3,300),"""","")
and copy down

And specifically for the data posted it looks first 66 characters need to be removed and double quote in end. If it is correct representation of your complete data then following normally entered formula can be used as well:
=SUBSTITUTE(MID(A1,67,300),"""","")
and copy down

And it can be implemented via VBA also like:

Public Sub TestCode()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("=INDEX(SUBSTITUTE(MID(" & .Address & ",67,300),"""""""",""""),0,0)")
End With
End Sub

[Jasa P]

Hi, thank you so much ZVI and shrivallabha :)
Both do work :) Thank you once again


Regards,
Jasa