Excel questions

[Ashtagon]

Two questions:

By default, the help search box will search through office.com for online help. I can manually set it to use only "Excel Help" on the local machine, but I have to redo that each time. Is there a way to make that the default?

I've set up an excel sheet to generate validated css3 code from a vast array of input cells. The output is a column of cells that I can copy and paste into notepad and then just save as is. Within Excel, things look fine. But when I paste, every cell worth of data has a double quote mark added before and after it (and actual double quote marks in the text get doubled up). This is apparently because Excel tries to export the text as valid csv text. How can I make it so the text gets pasted correctly. I don't want to have to go through an additional manual editing step if I can avoid it.

[Erloas]

First one I'm not sure on, if it is possible then there is probably a setting in the preferences somewhere.

As for the second... I know there are options when you export data, but I don't know for sure about just copy/paste. It is in the export options, I think there is also something in the preferences to designate text fields with " marks.

Sorry that is all a bit vague, but I don't have a copy of Excel on my home computer (using OpenOffice) and it might also change depending on what version of Excel you have.

[factotum]

It's actually possible to download the entire online help for Excel for faster access, as I recall--I don't actually remember how you do it, though. Something to do with opening the help program outside Excel and downloading the relevant help files?

[tomandtish]

Did you use alt-enter to create line breaks in your code? Some people do this to make the code look neater and easier to read, but it will add quotation marks.

Part of the problem is that excel likes to add quotes around different lines (anything that seems to be intended to be a different line anyway) in the same cell.

There's an easy test:

Type 3-4 letters in a cell, hit alt-enter, then 3-4 more characters and hit enter. Then copy the cell into note pad. If you got quotes, there's your problem. Any other functionality that creates separate lines can cause the problem as well.

Pasting into Word or Wordpad WILL work. it's just notepad that doesn't.

[Ashtagon]

Did you use alt-enter to create line breaks in your code? Some people do this to make the code look neater and easier to read, but it will add quotation marks.

Part of the problem is that excel likes to add quotes around different lines (anything that seems to be intended to be a different line anyway) in the same cell.

There's an easy test:

Type 3-4 letters in a cell, hit alt-enter, then 3-4 more characters and hit enter. Then copy the cell into note pad. If you got quotes, there's your problem. Any other functionality that creates separate lines can cause the problem as well.

Pasting into Word or Wordpad WILL work. it's just notepad that doesn't.

I don't use alt+enter. Everything is formula-based. I use the CHAR(10) function.

Here's a typical code snippet:

Spoiler

Show

=IF(S12=0,"",T12&" {"&CHAR(10)
&IF(BB12="",""," background-color: "&BB12&";"&CHAR(10))
&IF(AL12="",""," color: "&AL12&";"&CHAR(10))
&" font-family: "&IF(B12="","",B12&", ")&VLOOKUP(K12,'CSS3 Charts'!$B$3:$D$7,IF(B12="",2,3))&CHAR(10)
&IF(L12="",""," font-size: "&ROUND(IF(L12>=$L$6,LOG(L12/$L$6*2,2)*P12,L12/$L$6)*100,0)&"%;"&CHAR(10))
&IF(N12="",""," font-style: "&N12&";"&CHAR(10))
&IF(O12="",""," font-variant: "&O12&";"&CHAR(10))
&IF(Q12="",""," font-weight: "&Q12&";"&CHAR(10))
&IF(M12="",""," line-height: "&M12&";"&CHAR(10))
&IF(R12="",""," text-align: "&R12&";"&CHAR(10))
&"}")

I have found that running that through the clean() function to strip out the carriage returns (char(10))will result in no superfluous quote marks when I copy the text, but it also means the copied text won't contain carriage returns, and so won't be human-readable.

Is it really true that having superfluous quote marks in multi-line copied text is actually a feature and not a bug?

[factotum]

While I don't know if it's the answer to your issue, I wonder why you're using CHAR(10) as your line separator? The PC standard is CHAR(13)CHAR(10)--e.g. carriage return + line feed. (And note that CHAR(10) is line feed, not carriage return as you refer to it there--I don't know of any text dialect that uses just a line feed to mark the end of lines).

As for the whole bug/feature thing, it's most definitely a feature--when you cut and paste from Excel to something that only accepts text, Excel creates a valid CSV file because that's the only format that really makes sense in that situation. Believe me, I've had enough difficulties when exporting data from SQL server (which does NOT put quote marks around the individual fields) to know what a pain it is when those quotes are not included!

[Ashtagon]

While I don't know if it's the answer to your issue, I wonder why you're using CHAR(10) as your line separator? The PC standard is CHAR(13)CHAR(10)--e.g. carriage return + line feed. (And note that CHAR(10) is line feed, not carriage return as you refer to it there--I don't know of any text dialect that uses just a line feed to mark the end of lines).

Both char(10) or char(13) result in identical behaviour. And 10+13 together results in two new lines when pasted instead of a single new line.

[tomandtish]

It's not just going to be alt-enter. Char(10) or Char(13) or any other line marker will do th same thing.

This isn't really a problem I've had to deal with (I almost never use notepad), so I'm working on research here, but what I've found are two options:

1) Paste first into a text editor capable of handling RTF (Word, Wordpad, etc.) and then into notepad

2) Run a macro to automatically dump the data into a text file in the correct format.

One suggested macro can be found here. I haven't used it so make no recomendation about the reliability.

Short version: If you have to go Excel to Notepad, your options are limited. The quotes re going to want to show up, and there's no easy one click removal.

[Ashtagon]

I found a solution for #2.

http://www.flos-freeware.ch/notepad2.html

I set up excel to generate one line of text per cell. Then paste into notepad2. And a simple command-- ALT+R -- strips out all the blank lines. It means rejigging my spreadsheet again, but this gets the final result I need.

Now, about forcing the help files to default to local only?