Thursday, March 13, 2014

Excel help needed

Excel How can I turn rows or columns in an Excel file into multiple separate text files, one file per row/column?

Discussion on Google+


  1. Hi Alan,

    Sorry haven't got time to write either of these up properly but two options that you might be able to work with.

    Option 1. Use R. Save Excel as csv and import. Then use a for loop to split the data and save as necessary.

    for (x in 1:4){ ## norows = no of rows or columns
    subset <- data[x,] ## or ,1 if working with columns
    filename <- paste("row",x,".csv",sep="") ##you might want to change the filename
    write.csv(subset, file=filename) ##you might need to look at headings or saving as txt file

    Option 2 write an excel macro. Same principle loop over the data row by row or column by column. Copy the data in to a new sheet/workbook and save as txt/csv file.

    1. Thanks Brendan. As always, I suspect R is the best answer. Editing data in R is the area I'm weakest in, so this is a good opportunity for me to practice!