Excel 2016 Join entries in column A into one row in column b

[Cpreston]

Hi

I am trying to make one long row of information into one cell from many entry's in column A

so I could have 2oo rows in column A

A1 : 100001
A2: 200011
A3: 300000
etc....

I want to be able to make these entrys in Column A into one long entry in B1 with a comma between them.

B1 : 100001, 200011,300000 etc....

How can I do this in one command

Thanks

 

[Andrzejek]

Would the VBA solution be acceptable?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Yes VBA would be ok thanks

 

[Andrzejek]

Option Explicit

Sub Cpreston()
Dim intR As Integer
Dim strT As String

intR = 1

Do While Range("A" & intR).Value <> ""
    If intR > 1 Then
        strT = strT & ", "
    End If
    strT = strT & Range("A" & intR).Value
    intR = intR + 1
Loop

Range("B1").Value = strT

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I do something a little more generic with a user defined function to concatenate a range of cells

Function RangeConcatenate(rngIn As Range, _
        Optional strDelimiter As String = ", ")
[COLOR=#4E9A06][highlight #FCE94F]    ' Usage
    '  A1 = Red
    '  A2 = White
    '  A3 = Blue
    ' =RangeConcatenate(A1:A3) = Red, White, Blue
    ' =RangeConcatenate(A1:A3," - ") = Red - White - Blue[/highlight][/color]
    
    Dim rngTemp As Range              [COLOR=#4E9A06][highlight #FCE94F]'each cell[/highlight][/color]
    Dim strTemp As String
    Application.Volatile              [COLOR=#4E9A06][highlight #FCE94F]'autoupdates[/highlight][/color]
    For Each rngTemp In rngIn         [COLOR=#4E9A06][highlight #FCE94F]'loop through each cell[/highlight][/color]
        If Len(rngTemp.Text) > 0 Then [COLOR=#4E9A06][highlight #FCE94F]'ignore blank cells[/highlight][/color]
            strTemp = strTemp & rngTemp & strDelimiter
        End If
    Next
    If Len(strDelimiter) > 0 Then     [COLOR=#4E9A06][highlight #FCE94F]'remove the final delimiter[/highlight][/color]
        strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
    End If
    RangeConcatenate = strTemp
End Function

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Cpreston]

Thanks will give them a go and feed back

 

[Andrzejek]

I've tried to do it as simple and easy as I could, it could be a base to do what Duane did

What I would add:

Function RangeConcatenate(rngIn As Range, _
        Optional strDelimiter As String = ", ") [blue]As String[/blue]
...

And you don't really need this check, it is OK to subtract 0 :

[s]If Len(strDelimiter) > 0 Then[/s]     
    strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
[s]End If[/s]

---- Andy

There is a great need for a sarcasm font.

 

[combo]

With new excel (365, 2019) - apply TEXTJOIN function:

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

combo