Stop CONCATENATE("Hi",CHAR(10)) adding quotes around the resultant string when pasted to a text file?

Hi timtak,
I assume that you are saving your worksheet as a tab delimited text file.

What happens if you put a formula like this in every other row?
=CONCATENATE(A5,B5,C5,D5,E5,F5,G5,H5,I5,J5,K5,L5,M5,N5)

Notepad does not find any double quotes in the blank line in between rows in my Excel 2003 test workbook.

Brad

Dear Brad, Experts Exchange Genius

Thank you for your advice.

You are probably right. But in fact that is the way I found the file, with a blank line.
I just wanted to be clever and obviate the need for skipping a line all the time.
Please see...
> I can just leave a line between each question in the excel file

I am copying the concatenate row to a text file (via the clipboard). I guess
it is the fact that I am going via the clipboard that is causing the problem.
I guess that it is or windows that is adding the quotes around the string
which contains non-alphanumeric characters. But I am not sure.

I could put the concatenate into the first worksheet of a book, the bits
and bobs into the second worksheep and then save tab delimited and
throw away the last part of the excel file containing the non concatenated
bits and bobs.

But for the time being I am wondering if there is a way of avoiding those
quotes when I copy a EOL ended string from excel to a text file.

Tim

I tried saving the file as tab seperated text file but that did not work either - the mysterious
double quotes were included in the file. So it is excel that is adding the double quotes,
without being able to sense that they are there.

It seems that this must be pretty diffficult. I have put the point value to 500.

I don't think that this should effect things but I am now using this as the concatenate in order to put the correct answer in a random position and to add a line feed after each possible response.
=IF(I7=1,CONCATENATE("::",A7,"::",B7,"{",CHAR(13),CHAR(10),"=",C7,CHAR(13),CHAR(10),"~",D7,CHAR(13),CHAR(10),"~",E7,CHAR(13),CHAR(10),"~",F7,CHAR(13),CHAR(10),"}",CHAR(13),CHAR(10)),IF(I7=2,CONCATENATE("::",A7,"::",B7,"{",CHAR(13),CHAR(10),"~",D7,CHAR(13),CHAR(10),"=",C7,CHAR(13),CHAR(10),"~",E7,CHAR(13),CHAR(10),"~",F7,CHAR(13),CHAR(10),"}",CHAR(13),CHAR(10)),IF(I7=3,CONCATENATE("::",A7,"::",B7,"{",CHAR(13),CHAR(10),"~",E7,CHAR(13),CHAR(10),"~",D7,CHAR(13),CHAR(10),"=",C7,CHAR(13),CHAR(10),"~",F7,CHAR(13),CHAR(10),"}",CHAR(13),CHAR(10)),IF(I7=4,CONCATENATE("::",A7,"::",B7,"{",CHAR(13),CHAR(10),"~",F7,CHAR(13),CHAR(10),"~",D7,CHAR(13),CHAR(10),"~",E7,CHAR(13),CHAR(10),"=",C7,CHAR(13),CHAR(10),"}",CHAR(13),CHAR(10))))))

But the question is: please tell me how to stop excel putting quotes around
=Concatenate("Hi", CHAR(13),CHAR(10))

Aha...one way of reducing the number of extraneous quotation marks is to use another
concatenations of all the concatenations. This way at least there are only two unwanted
double quotes to remove, at the beginning and end of the file.
Tim

Hmm...Concatenate seems to have a limit of 36. I would like to be able to make
tests of at least 100 questions so I will have to Concatenate, Concatenations of
Concatenations. But I am getting there.

But the question remains, stop Excel from adding quotes!

Does anyone know why excel is adding quotes? I am not putting them there.

This must be a reall difficult problem, which is a shame because otherwise I quite
like the file that I have made
http://tim.econo.yamaguchi-u.ac.jp/temp/gift_maker.xls

I have waffled a lot in this thread but all I am asking is why does Excel add quotes to
=CONCATENATE("Hello",CHAR(10))
when it does not add quotes to
=CONCATENATE("Hello"," World")

Is this an Excel bug?

It's a "feature".

Excel adds the double quotes out of its never-ending zeal to be helpful. Excel's intent is to show that the results of your concatenation are all one string--there is no other way of knowing that on a multi-line concatenation. And Excel is trying to produce a text file that could be opened in Excel and still look as close as possible to the original.

Thanks again Brad

Ah...true, there would be no way of knowing that a multiline string is in fact one string unless the quotes where there.

So, bearing in mind Excel zeal, there is no way of removing the quotes?

Tim

Tim,
The simplest and easiest approach would be to put a blank row in your worksheet. If the blank spaces offend your sensibilities, set the row height of the blank rows to 0.

It would also be possible to write a macro that will take your spreadsheet without blank rows and output a text file without double quotes. It will do so by writing a line at a time directly to the text file.

Brad

Dear Brian,

Thanks again.

The blank row between each question is what I started with. I am asked this question because I wanted to do without the blank line.

I appreciate that having a blank line is not all that serious a problem, because that is what i started with.

But it is not just a question of my sensibilities, I think. Rather, if you are inputting text it is a drag to have to remember to skip a line.

If someone comes up with the macro that you mention then I will be most grateful.

Tim

Sorry I got your name wrong, Brad (not Brian).
Tim

Dear Brad

Thank you very much indeed.

But alas, when I execute the above I get a 1004 error and the debug stops on
    wsCopy.SaveAs Filename:=flPath, FileFormat:=xlTextWindows
and the excel file itself has been overwritten (adding in blank lines every other line).

Perhaps the problem could be that my selection is only one cell ("C1").

Following your suggestion above, I was trying and failing dismally with Earl K... and cpearsons' macros.
http://www.cpearson.com/excel/imptext.htm
The latter looked hopeful but I could not get out of debug mode.

Aha....I have managed to get a modified version of cpearson's to work as below. There is a lot in this that I do not need and I will try to brush it up a bit....I can't seem to set it to export C1 only though but would that be another question?

I am working on it.

Public Sub Gift()
Dim FName As Variant

FName = Application.GetSaveAsFilename("hello.gft", filefilter:="Text Files (*.txt), *.txt", _
    Title:="Please pick a file name for your text file")
If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If


ExportToTextFile CStr(FName)
End Sub

Public Sub ExportToTextFile(FName As String)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim Sep As String
Sep = ""

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Selection
    StartRow = .Cells(1).Row
    StartCol = .Cells(1).Column
    EndRow = .Cells(.Cells.Count).Row
    EndCol = .Cells(.Cells.Count).Column
End With


Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

Aha, "C" does not work as column but the following does
   StartRow = 1
    StartCol = 3
    EndRow = 1
    EndCol = 3

Thanks. It is done.

For all those that come afterwards while the "print" commade will indeed print to a file without quotes, it has a limit of 1024 characters (I think there were about 960 characters and some line spaces) which is the same as the limit that can be displayed in a cell. So while I managed to concatenate everything in the one cell to be copied (since the limit is massive, 32 thousand characters I believe) when I went to "print" that cell, only the first 1000 or characters went to the file. Hence it is important to use a loop to print out all the cells.