Inverse function of convert in Excel

Question

Hello, I would like to know if anyone knows how to get the inverse function of converting in Excel

Example:
I have 3 columns and lots of rows, I want to combine all that into a single column with the values separated by commas.

123 srv5 pc5
124 srv6 pc6
125 srv7 pc7
....
...
...

In 3 columns, this would give me the following in one column

123, srv5, pc5
124, srv6, pc6
125, srv7, pc7
...
..
...

Configuration: Windows XP / Internet Explorer 6.0

chossette9 · Answer

Hello,

assuming that your values are in columns A, B, and C, then write in D1:

=A1&","&B1&","&C1

or

=CONCATENATE(A1;",";B1;",";C1)

then drag down (position your cursor in the bottom-right corner of the cell, wait for the cursor to turn into a cross, click and drag down to the desired row).

Best regards.
--
That guy was not very bright, as he was foolish.

ADZ · Answer

Hello I don't know if that's what you're looking for, but there is the concatenate function that combines text strings from multiple cells into a single cell.

audren49 · Answer

Hello,

I have roughly the same issue except that my Excel table uses almost all the columns of the sheet... (I want to replace all these columns with commas for example)

The concatenate function therefore seems tedious...

Do you have another solution?!

Thank you,

ccm81 · Answer

Hello everyone

Indeed, a macro should do the trick, something like this

Option Explicit Const separator = "," Const FD = "Sheet1" ' data sheet Const startRowFD = 4 Const codeColumnFD = 1 Const FR = "Sheet2" ' result sheet Const startRowFR = 4 Const codeColumnFR = 1 Public Sub ConcatenateColumns() Dim liFD As Long, lastRowFD As Long Dim coFD As Long, lastColFD As Long Dim liFR As Long Dim s As String ' last row sheet FD lastRowFD = Sheets(FD).Cells(65536, codeColumnFD).End(xlUp).Row ' initialization liFR liFR = startRowFR ' let's go For liFD = startRowFD To lastRowFD s = "" lastColFD = Sheets(FD).Cells(liFD, 255).End(xlToLeft).Column For coFD = codeColumnFD To lastColFD - 1 s = s & Sheets(FD).Cells(liFD, coFD).Value & separator Next coFD s = s & Sheets(FD).Cells(liFD, lastColFD).Value Sheets(FR).Cells(liFR, codeColumnFR).Value = s liFR = liFR + 1 Next liFD End Sub

Have a great day!

Inverse function of convert in Excel

4 réponses

Texture issues in enshrouded

Old lullaby

Unable to connect to freebox os

Edit ad on leboncoin for free

Freebox wifi password

Computer tower that won't turn on

Blocked: are my messages still being delivered?

Video intercom inspector

Your computer can't use another display.

Fransat decoder stuck