I need your help to remove some unnecessarily quotes from a CSV file. This file contains thousands of rows, data is stored in column A and is like this:

185552,"NAME",0900000000,[email protected],"2021-10-30 10:20:56",525.00,EUR,56183498348,"2021-10-30 10:20:56",2021-10-31,confr,53654189,13031062,1,0.5,"9658222K","Bank",,4433351,642,"XID 4211236",ENCC

To detect this unnecessarily quotes I recorded a macro to duplicate column A, then do text to columns in column B, with comma as delimiter and none as TextQualifier:

Columns("A:A").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
        :=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array( _
        Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
        Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, _
        1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
        Array(21, 1), Array(22, 1)), TrailingMinusNumbers:=True

After running this macro some rows in column R contains data which are wrapped between double quotes. In my example above this value 9658222K is wrapped between double quotes "9658222K". Due this annoying issue I have to manually locate those double quotes in column R and delete them from the corresponding row in column A. I will be extremely grateful if you have some vba code to do that job automatically. Thank you.

CodePudding user response：

I'm not sure what is causing this for you. When I put your example data in a csv file and open it in Excel, the quotes you describe are absent. However, if you are interested in removing leading and trailing quotes from cells in a given column. This code will do the trick.

Sub run_remove_quotes()
    remove_quotes ("R") ' Removes the leading and trailing quotes from all cells in column R
End Sub

Sub remove_quotes(column As String)
    Dim s As Worksheet
    Set s = ActiveSheet ' use this line to process the active sheet
    'Set s = Worksheets("Sheet1") ' use this line to process a specific sheet
    
    Dim cell As Range
    
    For Each cell In Intersect(s.Cells(1, column).EntireColumn, s.UsedRange)
        If Left(cell.Value, 1) = Chr(34) And Right(cell.Value, 1) = Chr(34) Then
            cell.Value = Mid(cell.Value, 2, Len(cell.Value) - 2)
        End If
    Next

End Sub

Just execute the procedure named "run_remove_quotes" supplying the column letter you want to process

CodePudding user response：

I'm sorry, guys, if I haven't been detailed enough about my problem. I will try again to better explain what I need. I will try to replicate below the steps performed to remove unnecessarily quotes from a CSV file:

1. duplicate column A in column B;
2. execute text to columns in column B with the following parameters:comma as delimiter and none as TextQualifier (none as TextQualifier helps me to expose unnecessarily quotes, if I use DoubleQuote as TextQualifier all quotes are gone and I dont want that).
3. locate in column Q (by mistake I mentioned earlier column R) all cells which contains quotes;
4. remove those quotes from column A, not from column Q. But there is a catch, in column A are good quotes which shouldn't be eliminated. I need to eliminate those harmful quotes in colomn A basesd on the finding in column Q. My aproach is to duplicate column Q to R, then remove all quotes from column Q. The data will be like this:

A Q R

.....,"......,"9658222K",.... 9658222K "9658222K"

All i need is some vba script to copy value from R and replace it in A with value from Q using find and replace with parameter MatchCase:=True, then loop to find another quotes in column R.

Range("A1").Select
    Cells.Replace What:="""9658222K""", Replacement:="9658222K", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

Please bear in mind that valule wrapped between quotes is variable.

Espected result:

A                                   Q           R
.....,"......,9658222K,....   9658222K   "9658222K"

Thank you.