We can use the Row
command to convert a list into comma separate values. However, we must convert the row into a string before we export. If we do this
Export["tmp1.xls",
ToString[Row[#,","]]& /@ list
]
then we get an Excel file with one column that contains only text data. Even if we set a1=3.14
in MMA, it will be text in Excel.
If our row contains only one value we do not need the Row
command or the ToString
command. So, we could do this
Export["tmp2.xls",
If[Length[#] > 1,
ToString[Row[#, ","]],
First[#]] & /@ list
]
The second version will export rows that contain single numbers as numbers.
So far we have only considered a list of lists. What if we want to export this: list3 = { 3.14, {1, 4, 9} };
? We would want the number 3.14 to appear in Excel, along the text "1, 4, 9". Here is how we could do it.
list3 = {3.14, {1, 4, 9}};
Export["tmp3.xls",
Which[
Head[#] === List,
If[Length[#] > 1,
ToString[Row[#, ","]],
First[#]],
True, #] & /@ list3
]
This code checks to see if the element is a list. If it is a list, and its length is greater than 1, we need to convert it to a row. Otherwise, we do not change the element.
Flatten[list]
to get rid of all sub lists. $\endgroup$ – Sjoerd Smit Dec 18 '17 at 15:20