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