convert Excel file to text 1

[mtdew]

I have an Excel file with data in the columns A-E. I need the Excel file converted to a text file so that looks like this:

question[1]=new Array("INFO IN CELL A1 ","INFO IN CELL B1","INFO IN CELL C1","INFO IN CELL D1","INFO IN CELL E1",1);

And then question[2] would be the same except it would contain the data found in the excel file row 2.

My Excel data is over 1100 rows so doing it manually would be VERY tedious. Is it possible to convert this in Excel?

 

[Gavona]

Construct the value you want in column F,
Copy, PasteSpecial Values
Then delete columns A:E
Finally Save As a text file

The formula in F1 would be
="question["&ROW()&"]=new array("""&A1&""""&B1&""""&C1&""""&D1&""""&E1&""""&",1);"

This formula should be filled down.

Gavin

 

[Gavona]

OOPs that should be:
="question["&ROW()&"]=new array("""&A1&""","""&B1&""","""&C1&""","""&D1&""","""&E1&""","""&",1);"

Gavin

 

[Gavona]

Further thought: The solution as posted will leave you with quotes around the result:
"question[1]=new array(""a"",""b"",""c"",""d"",""e"","",1);"
"question[2]=new array(""a"",""b"",""c"",""d"",""e"","",1);"

This is because: If a cell contains a comma, the cell contents are enclosed in double quotation marks.

If this is a problem then replace the commas with some other character or characters. Create the file. Load into Notepad and use Edit, Replace to get your commas.

Gavin