Save Excel Files as Text Without Quotes (4 Methods)

When you try to save an Excel worksheet as a text file, the columns can be separated by double quotation marks. You can avoid this issue by following different techniques. In this article, you will learn 4 methods that you can use to save Excel files as text without quotes.

Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.

Save Excel Files as Text Without Quotes.xlsm

4 Methods to Save Excel Files as Text Without Quotes

1. Use Text (Tab Delimited) Format to Save Excel Files as a Text Without Quotes

Following this method, you can directly save your Excel worksheet as a text file with no quotation mark at all. To do that,

❶ Go to the FILE menu first.

❷ Then click on Save As.

❸ After that, you will see lots of saving options. Select Text (Tab delimited) and hit the SAVE command.

Now if you open the text file in Notepad, you will see that there are no quotes in the text file at all.

2. Copy Data and Paste It to Notepad to Save Excel Files as a Text Without Quotes

You can copy your data from an Excel worksheet and paste it to Notepad to avoid unnecessary quotes. To do that,

❶ First select the cells in your Excel worksheet that you want to copy. To copy you can use CTRL +C keys together.

❷ Now open the Notepad and paste your data by pressing CTRL + V keys together.

Following this method, you can easily get rid of the quotes in your text file.

3. Use Find and Replace to Save Excel Files as a Text Without Quotes

To remove the quotes from your text file, you can copy them to Microsoft Word. Then you can find and replace those quotes with empty strings. After that, you can copy them back to your text file. This is another way to remove quotes from your text file.

To do that,

❶ Open your text file with quotes and copy them using CTRL + C.

❷ Now open Microsoft word and paste your copied data using CTRL +V from the clipboard.

❸ After that press CTRL + H to open the Find and Replace dialog box.

❹ Enter a double quotation (“) in the Find what box.

❺ Leave the Replace with box blank.

❻ Then hit Replace All command.

So you’ve removed all the quotation marks from your text. Now copy them back to the Notepad and you are done!

Similar Readings

4. Use VBA Code to Save Excel Files as a Text Without Quotes

4.1 VBA Code #1

This VBA code can directly save your Excel workbook as a text file without quotes. To use the VBA code,

❶ Press ALT + F11 to open the VBA editor.

❷ Go to Insert. Then to Module.

❸ Copy the following code to the VBA editor and save it.

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'Change the file_location and filetype accordingly
Const FlName = "C:\Users\mahdy\desktop\data.txt"

Sub Sample1()
    Dim tempFile As String
    Dim CurrentData As String, strData() As String
    Dim Line As String
    Dim WorkbookSize As Integer

        tempFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"

    ActiveWorkbook.SaveAs Filename:=tempFile 
    , FileFormat:=xlText, CreateBackup:=False

        Open tempFile For Binary As #1
    CurrentData = Space$(LOF(1))
    Get #1, , CurrentData
    Close #1
    strData() = Split(CurrentData, vbCrLf)

WorkbookSize = FreeFile()


    Open FlName For Output As #WorkbookSize

    For i = LBound(strData) To UBound(strData)
        Line = Replace(strData(i), """", "")
            Print #WorkbookSize, Line
    Next i

    Close #WorkbookSize




End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

❹ Update the directory path of your output file.

❺ Now press the F5 button to run the code.

After hitting the F5 button, you will see that your Excel sheet has been saved as a text file to your specified directory. If you check the text file, you can see that there are no quotes at all.

4.2 VBA Code #2

You can save a specific portion of your Excel worksheet as a text file using the following VBA code. To do that,

❶ Press ALT +F11 to open the VBA editor.

❷ You need to create a new module now. To do that, go to Insert>Module.

❸ Copy the following VBA code and paste it to the VBA editor.

❹ Save the following code.

Sub Export()

Dim r As Range, c As Range

Dim sTemp As String

Open "C:\Users\User\Desktop\Softeko\Data.txt" For Output As #1

For Each r In Selection.Rows

sTemp = ""

For Each c In r.Cells

sTemp = sTemp & c.Text & Chr(9)

Next c

While Right(sTemp, 1) = Chr(9)

sTemp = Left(sTemp, Len(sTemp) - 1)

Wend

Print #1, sTemp

Next r

Close #1

End Sub

❺ Create a txt file and update the directory of the file in the code. Your selected cells will be directly saved into this text file without quotes.

❻ Now go back to your Excel worksheet and select the cells that you want to export.

❼ Now go to DEVELOPER > Macros. Or press ALT + F8 to open Macros.

❽ Select Sheet6.Export and hit the Run command.

Now you will see that your select cells have been saved as a text file without quotes at your specified directory.

Things to Remember

📌 Press ALT + F11 to open the VBA editor.

📌 Use ALT + F5 keys to run the VBA code.

Conclusion

To sum up, we have discussed 4 methods to save Excel files as text without quotes in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.

Get FREE Advanced Excel Exercises with Solutions!