13

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.

3
  • 1
    What is wrong with saving it as a .csv?
    – soandos
    Feb 8, 2012 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, 2012 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. Feb 8, 2012 at 11:11

5 Answers 5

8

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.

8

In excel there is a function called TEXTJOIN. It joins texts from multiple columns using user-provided delimiter.

It requires three parameters:

  1. delimiter: what delimiter to use. In your case, it is ";"
  2. ignore empty cells: true(ignores empty cells) or false(doesn't ignore)
  3. text: you can provide range. e.g. A1:A6 in your case.

    =TEXTJOIN(";",FALSE,A1:A6)
    

Then you can drag the formula to copy the same in multiple rows and then copy and paste it as Values.

Hope it helps. BTW I am using Excel 2016, I dunno if this function is available in older versions..

Cheers.

1
  • Only available in Office 2016 and 365
    – jkd
    Jan 3, 2020 at 17:09
6

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

2

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

1

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.

1
  • This is the best answer and should be used.
    – Joop
    Nov 7, 2015 at 11:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .