escape semicolon character when writing to csv file

Question

I want to write something like

=HYPERLINK("http://example.com"; "Example") 

to a comma-separated CSV file, but Excel parses the semicolon and puts "Example") part in another cell. I tried escaping the semicolon with backslash and wrapping everything in double quotes without any luck.

Any help?

Answer 1

The wrapping with double quotes was already the correct idea, but you have to make sure you do it correctly. You can put a column within double quotes, then everything inside is considered as a single value. Quotes itself have to be escaped by writing two of them ("").

See for example this:

Column A;Column B;Column C Column A;"Column B; with semicolon";Column C Column A;"Column B"";"" with semicolon and quotes";Column C Column A;"=HYPERLINK(""http://example.com""; ""Example"")";Column C 

Answer 2

I also had a very wild time tring to figure the whole picture out, then is how I've got all my csv ready to open into excel in php ( which includes utf8 encoding as well ) :

$sep='";"';//note the separator is double quoted while($t=mysql_fetch_assoc(mysql_query('select ..')){   #replaces the caracters who are breaking csv display   $t=array_map(function($x){return str_replace(array("\n","\r",'"'),array('\\n',"\\r",'""'),$x);},$t);   $csv.="\n\"".implode($sep,$t)."\""; } $charset='utf-8'; header('Content-Type: application/csv;charset='.$charset); header('Content-Disposition: attachment; filename="filename.csv"'); $bom=chr(239).chr(187).chr(191);#this tells excel document is utf8 encoded die($bom.$csv);