Odd cel behavior with .txt files

[AtLarge]

Hi, hopefully someone can help be understand what's going on here. When I create a tab delimited file in Excel it is putting qoutation marks in the cel. It only does it to B1 but not A1 or C1. I know it has something to do with the comma because if I take it out of the name it doesn't add the qoutes. How do I keep this from happening other than reopening the file in WordPad and removing all the qoutes?

 

[Brianwarnock]

I had no problem with your example.

Brian

Office 2002 sp3

 

[Bilbo_Baggins_Esq]

I think hes actually talking about what the text looks like when it has been saved as text, not as an XLS.

I too can take his text and either paste it, or open it directly in Excel and the quotes are gone, but in the text file, they are present.

I believe this is "by design" and is used as a text qualifier when importing into other sources, or back into Excel.

If you really need to get it saved as text, in a tab deliminated format, without the quotes, I think you'll want to try and save it as the type "Text (MS-DOS)".
It is still text,
It is still tab delimited,
but it does not add the quotes because you are essnetially telling Excel you are not going to need any text qualifiers at your import location.

 

[AtLarge]

Yes! BBE you are spot on. The problem I have is I am creating a text file to be uploaded to SAP. It must be tab delimited .txt and for editing purposes it's very difficult to copy and paste to anything but Excel and still keep all the columns and data straight. I tried you using the MS-DOS .txt but it put the qoutes in too.

I even tried to open the file with Excel and use the import wizard. Even changing the text qualifier to "none" and saving it again still puts the qoutes in. Weird. Any other suggestions would be appreciated.

 

[Brianwarnock]

Since help states, when saving as .txt tab delimited

If a cell contains a comma, the cell contents are enclosed in double quotation marks.

I don't see how you can keep the comma and avoid the quotes.

Brian

 

[Bilbo_Baggins_Esq]

I’ve worked with SAP guys before, and I would be shocked if SAP could not, or would not be able to recognize quotes and what they mean, and how to process it.

Tabs and commas are internationally recognized field delimiters and quotes are internationally recognized text identifiers.
There is zero doubt in my mind that the SAP developers are aware of this.

Anyway, I don't know what the bleep happened yesterday, or how it actually happened, but yesterday, I know I saved the file as MS-DOS text and there were no quotes.
Today, I am unable to reproduce that.
Fortunately, I know I did not hallucinate it because I actually do have the file to prove it.
How it happened, I apparently cannot say.

Here is a work around that I know works (because I’ve checked it several times).
Copy your finished data in Excel and paste it into Word.
In Word, select all the data and convert the table to text.
A dialog box will ask you to select a delimiter, select TAB.
Then save the file as plain text.
You will not have any quotes.

 

[chergh]

You could write some vba to manually output your spreadsheet to a text file.

sub blah()

Dim TxtToWrite as string
dim i as integer

for i = 1 to 10

TxtToWrite = worksheets("Sheet1").range("A" & i).value & vbtab & _
                   worksheets("Sheet1").range("B" & i).value & vbtab & _
                   worksheets("Sheet1").range("C" & i).value & vbcrlf
next i

open "C:\blah\filename.txt" for append as #1

print #1, TxtToWrite
close #1

end sub

 

[AtLarge]

Thanx everyone. Good suggestions. I've been limping along by doint all the editing in Excel and then before I upload, open the text file in Notepad and edit replace all the " with nothing. Then save and close. I couldn't explain why Excel was doing what it was so I thought someone else might know.

The code do save it outright is a good idea. I'll try working with that too.