Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm new here and I hope to seek for your assistance on converting data format of my excel to csv.

This is my raw data in excel in different row and cells:

A   B   C   D   E   F
1   2   3   4   5   6
2   3   4   5   6   7
3   4   5   6   7   8

I would like to convert all data into this format containing 1 column and different rows:

A;B;C;D;E;F
1;2;3;4;5
2;3;4;5;6
3;4;5;6;7

As I have large volume of data to be processed, a convenient way of converting the data is really needed.

share|improve this question
1  
What is wrong with saving it as a .csv? –  soandos Feb 8 '12 at 8:37
    
Sorry, i didn't make my question clear. I mean I would want to convert the from multiple columns to single column with data separated by ";" –  Seewhy Feb 8 '12 at 9:03
    
I have changed the title, as it seems your objective is not to create a normal CSV file but to combine columns in Excel. If I misunderstand, please undo the change and/or update the question to clarify the objective. –  RedGrittyBrick Feb 8 '12 at 11:11

4 Answers 4

You can use this formula to merge one row to only one column:

= A1 & ";" & B1 & ";" C1

Then, copy this formula to all rows and use the column you created where you want.

share|improve this answer

You can use the concatenate function =concatenate(A1,";",B1,";",C1 ... you then drag it down the entire height of your data. Once you've done this you copy and paste special - values to keep only the concatenated values. You can then delete everything except your pasted column and save as .csv

share|improve this answer

Old question but just in case others stumble upon it here's a solution that is easier to implement with more data.

The concatenate function is indeed the key but if you are dealing with a lot of data selecting each cell can be a tedious thing indeed.


So let's take that example, a table from A1 to F4.

In a cell to the right insert =A1, let's say G1 for this example (using cells next to your data allows you to double click the bottom right corner of the cell and auto fill them up to the length of the adjoining filled column, or you can have the whole thing inside a table and they autofill on their own)

Then in H1 insert =concatenate(G1,";",B1)

Drag this formula out the same number or columns that your data has and it will gradually concatenate all the data.

The very last column will have the desired output, that would be column L in this case, cells L1:L4.

Do what you wish with said values.


You an also use this to concatenate an entire column. Or to have conditional concatenating (just add an if in each cell to decide whether to concatenate or not)

As an afterthought, if you wish to do this only once it is a lot easier to just copy the table into word and convert to text using ; as the separator

share|improve this answer

If there's no spaces or commas within the cells, you could convert to CSV then use a text editor to find/replace commas with semicolons, then re-load in Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.