Geek Answers Handbook

Is there a way in Excel to take a column and convert it to a concatenated row

I want to take a list of names that are on several lines and combine them into one cell as follows:

Joe
Bob
George

and convert it to one cell that has this:

Joe, Bob, George

+5
source share
6 answers

If the ExcelCONCATENATE() function won't do what you want, this link may help.

+1
source

Try using the TRANSPOSE function and pressing F9 on it:

A1:

Joe

Bean

George

B1: (enter into the function panel)

= TRANSPOSE (A1: A3)

TRANSPOSE (A1: A3) F9.

:

{ "", "", "" }

. .

, :

= ( (1: 3))

TRANSPOSE (A1: A3), F9, :

= CONCATENATE ( "", "", "" )

+6

, .

-

  • A
  • A1 B1 - "Joe"
  • B2 `= B1 & "," & A2`
  • B2, B A. B , , A.

-
vb, , , sum, .

, , - Excel, - MS, , .

+3

. , , . !

= A1 & " " & B1

+1

Excel

Function Concat(myRange As Range, Optional myDelimiter As String) As String
  Dim r As Range
  Application.Volatile
  For Each r In myRange
    If Len(r.Text) Then
      Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text
    End If
  Next
End Function
+1

GetString ADO.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String

''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet1$A:A] "

rs.Open strSQL, cn, 3, 3

''http://www.w3schools.com/ado/ado_getstring.asp
''str = rs.GetString(format,rows,coldel,rowdel,nullexpr)
s = rs.GetString(, , , ", ")
ActiveSheet.Range("B1") = s

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
0

More articles:


All Articles