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.
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!
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.
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.