Concatenate Strings in each cell

[excelkeechak]

hi, i want to concatenate each cell in a range.how would i do it.

[Paul]

Hi Excelkeechak,

Can you provide a bit more detail about your situation? For example, do you have two or more columns of data (let's say A, B and C) and you want to concatenate A1, B1 and C1, then A2, B2 and C2, etc.? Or is your setup different?

Once you give us a direction we'll be able to provide specific help.

In general, you can concatenate fields using worksheet functions in a couple ways:

=CONCATENATE(A1,B1,C1) <--- This would concatenate A1:C1 with no spaces

or

=A1&B1&C1 <--- This would concatenate A1:C1 with no spaces

or

=A1&" "&B1&" "&C1 <--- This would concatenate A1:C1 with spaces between each

[excelkeechak]

i know the excel general formula but what i need is.

A
Apple
Boy
Cat
Dog
Egg
Hen
etcc..

I want to concatenate like this using VBA
Apple, Boy,Cat,etc...(Words or numbers separated using Comma)

[Paul]

Two ways, one using formulas, the other using VBA:

Using Formulas:
In B1 put the formula =A1
In B2 put the formula =B1&","&A2
Fill the formula in B2 as far down as you have values in column A. The string in column B will keep getting longer as it adds the new cells values from column A. Once at the bottom, copy the last value in column B to another cell, but use PasteSpecial -> Values (otherwise you'll get no result when you delete all the other unnecessary values above in column B).

Using VBA:
Right-click on the worksheet tab with your values and select 'View Code'
Paste the following code into the VB Editor window, then close the VB Editor
Press ALT+F8 then select the "joinData" macro and click Run.

This macro concatenates every value from A1 to the bottom of column A, using a comma as the delimiter. It then inserts the result into cell B1 as one long string. You can obviously adjust the code to suit your needs.

Sub joinData()
Dim myVals() As String, output as String
Dim i As Long, N As Long

N = Sheets("Sheet1").Range("A65536").End(xlUp).Row
ReDim myVals(N - 1)
For i = 1 To N
    myVals(i - 1) = Cells(i, 1).Value
Next i

output = Join(myVals, ",")
Sheets("Sheet1").Range("B1").Value = output
End Sub

[Phil_V]

How many rows do you have?