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","","","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. 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. 96.69.35.53.09. 90,46.64.x8./57.75.68.86x20./

ROW 3
sms,"READ,RECEIVED","+6281363105225","","","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. 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. 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