Double Quote seems not work for me. Help

[Flicker]

Hello all,

Based on VBA script below, I can add double-quote cover my string.

Sub Add_DQuote()
    Dim r As Range
    With Selection
        For Each r In Selection
        'MsgBox IsNull(r.Value) = True
        'MsgBox Mid(r.Value, 1, 1)
        'MsgBox Mid(r.Value, Len(r.Value), 1)
        'MsgBox Mid(r.Value, 1, 1) = """" And Mid(r.Value, Len(r.Value), 1) = """"

         If Len(r.Value) > 0 Then
        
        'If cell is null then mid will error because start character is zero. Use the above If to prevent this error.

            If (Mid(r.Value, 1, 1) = """" And Mid(r.Value, Len(r.Value), 1) = """") Then
                r.Value = r.Value
            Else
                If IsNumeric(r.Value) = True Then
                    r.Value = r.Value
                Else
                    r.Value = """" & r.Value & """"
                End If
            End If
        End If
        Next
    End With
End Sub

So, when I apply the above script for an area of text below;

AAA
BBB
CCC

I would get a result

"AAA"
"BBB"
"CCC"

however, when I save it as a CSV or Text file. The text file show contest as;

"""AAA"""
"""BBB"""
"""CCC"""

what ?!?

I'm also use LEN() to count the lenght of my "AAA" and it return 5.

I try again to leave both "AAA" and AAA in the same file

"AAA"
"BBB"
AAA


and I save the file to CSV. The result is

"""AAA"""
"""BBB"""
AAA

hmm.. so strange!

The result I want is;

"AAA"
"BBB"
AAA

Could anyone point me out what I did is wrong please?

Best Regards,
Flicker

 

[GTO]

Hi Flicker,

I just ran your code in a new wb, copied the sheet to a blank wb and savedas a csv. Proper double quotes surrounded the vals. How are you saving?

Mark

 

[Flicker]

Thank you, GTO.

I choose file > save as > csv comma delimited

now I work around with replace """ to " in notepad++ :'(

 

[GTO]

Thank you, GTO.

I choose file > save as > csv comma delimited

now I work around with replace """ to " in notepad++ :'(

Okay, I'm stepping out past what I know about text vs csv, but I did see that it I opened the created csv with notepad or another text editor, indeed, the triple-double quotes would exist.

What is the goal? Are you wanting not a csv file, but a textfile?

 

[Flicker]

Hi GTO,

I want to save Excel as a comma separate for each column. and string (text) value must have a double-quote (") append before and after the text.

The limitation of Excel is it can save as a CSV which it use comma separate each column but it does not have option for double-quote to cover text.

That's why I try to wrote the macro for this purpose. It look beautiful in Excel file but when I try to save it as CSV file it give me extra two double-quote that I do not need. T_T

 

[GTO]

Okay, what I am saying though is that after saveas the .csv, if you open the .csv in Excel, the double quotes are correct. The "triple-double" quotes (for my lack of a better descript) only show if the csv is opened in a text editor. Is this a concern?

 

[Flicker]

ar.. sorry that I'm not understand you first time GTO. yes, I concern because I open an CSV file in excel. Then, I run Macro and save it as CSV. This file will be use to import in another application. So, the format must be CSV, cannot be Excel. I still have no idea why it show """...""" in CSV file if open it with notepad but in Excel it show "..." perfectly.