Excel - Tab delimited with Quote identifier

Posted on 2012-08-24
Last Modified: 2012-09-04
I have an excel that I need help with converting to a tab delimited with quote identifier.  When I tried to save the excel file, it is saving it text delimited, but having issue with not being able to use quote around each cell as identifier.

I know that if there's comma, then it'll put quote around it.  However, that also create another issue due to some of my cells having comma in there for like description.

Anyone has a script or something that can do similar that they can share with me?


ID          Description       Qty        Amount
122        XLS 5', 3x4         15           $2,000.00

I need it as followed:
"ID"         "Description"       "Qty"        "Amount"
"122"        "XLS 5', 3x4"         "15"           "$2,000.00"

Right now it is showing like this:
ID          Description       Qty        Amount
122        "XLS 5', 3x4"         15           "$2,000.00"
Question by:holemania
    LVL 39

    Accepted Solution

    Try this code, taken from here. Select your data, then run ExportText


    Sub ExportText()
    ' ExportText Macro
    Dim delimiter As String
       Dim quotes As Integer
       Dim Returned As String
      delimiter = vbTab
      quotes = MsgBox("Surround Cell Information with Quotes?", vbYesNo)
    ' Call the WriteFile function passing the delimiter and quotes options.
          Returned = WriteFile(delimiter, quotes)
       ' Print a message box indicating if the process was completed.
          Select Case Returned
             Case "Canceled"
                MsgBox "The export operation was canceled."
             Case "Exported"
                MsgBox "The information was exported."
          End Select
       End Sub
       Function WriteFile(delimiter As String, quotes As Integer) As String
       ' Dimension variables to be used in this function.
       Dim CurFile As String
       Dim SaveFileName
       Dim CellText As String
       Dim RowNum As Integer
       Dim ColNum As Integer
       Dim FNum As Integer
       Dim TotalRows As Double
       Dim TotalCols As Double
       ' Show Save As dialog box with the .TXT file name as the default.
       ' Test to see what kind of system this macro is being run on.
       If Left(Application.OperatingSystem, 3) = "Win" Then
          SaveFileName = Application.GetSaveAsFilename(CurFile, _
          "Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
           SaveFileName = Application.GetSaveAsFilename(CurFile, _
          "TEXT", , "Text Delimited Exporter")
       End If
       ' Check to see if Cancel was clicked.
          If SaveFileName = False Then
             WriteFile = "Canceled"
             Exit Function
          End If
       ' Obtain the next free file number.
          FNum = FreeFile()
       ' Open the selected file name for data output.
          Open SaveFileName For Output As #FNum
       ' Store the total number of rows and columns to variables.
          TotalRows = Selection.Rows.Count
          TotalCols = Selection.Columns.Count
       ' Loop through every cell, from left to right and top to bottom.
          For RowNum = 1 To TotalRows
             For ColNum = 1 To TotalCols
                With Selection.Cells(RowNum, ColNum)
                Dim ColWidth As Integer
                ColWidth = Application.RoundUp(.ColumnWidth, 0)
                ' Store the current cells contents to a variable.
                Select Case .HorizontalAlignment
                   Case xlRight
                      CellText = Space(Abs(ColWidth - Len(.Text))) & .Text
                   Case xlCenter
                      CellText = Space(Abs(ColWidth - Len(.Text)) / 2) & .Text & _
                                 Space(Abs(ColWidth - Len(.Text)) / 2)
                   Case Else
                      CellText = .Text & Space(Abs(ColWidth - Len(.Text)))
                End Select
                End With
       ' Write the contents to the file.
       ' With or without quotation marks around the cell information.
                Select Case quotes
                   Case vbYes
                      CellText = Chr(34) & CellText & Chr(34) & delimiter
                   Case vbNo
                      CellText = CellText & delimiter
                End Select
                Print #FNum, CellText;
       ' Update the status bar with the progress.
                Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
                   + ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
       ' Loop to the next column.
             Next ColNum
       ' Add a linefeed character at the end of each row.
             If RowNum <> TotalRows Then Print #FNum, ""
       ' Loop to the next row.
          Next RowNum
       ' Close the .prn file.
          Close #FNum
       ' Reset the status bar.
          Application.StatusBar = False
          WriteFile = "Exported"
       End Function

    Open in new window


    Author Comment

    Thanks.  That'll work.

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now