Concatenate cells to form a string. 2

[DPlank]

Hi. I have a series of numbers in a list of cells, 1 per row. I want to return the full list of numbers, separated by a comma, into a single cell.

Is this possible? If so, how?

ie.
1
2
3
4
5
becomes 1,2,3,4,5

Thanks in advance
David

 

[vextax21]

Do you want to use VBA?

 

[Dreamboat]

=A1&", "&A2&", "&A3&", "&A4&", "&A5

If you don't want spaces, and only want commas:

=A1&","&A2&","&A3&","&A4&","&A5

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[DPlank]

I am happy to use VBA if someone will explain it to me first...

I was also aware of the concatenate operation, but in this case I'd prefer to use something less manual, as I don't have an exact count of the numbers in the row. There may be 2 numbers to string together, or there may be 20. So I need to limit it without having a bunch of commas appearing at the end.

Thanks
David

 

[DaleWatson123321]

David,

Here a formula solution...

This formula is based on your example of up to 20 numbers. You could expand the formula beyond 20 numbers if required.

Copy this formula into cell A21.

=LEFT(A1&","&(A2)&","&(A3)&","&(A4)&","&(A5)&","&(A6)&","&(A7)&","&(A8)&","&(A9)&","&(A10)&","&(A11)&","&(A12)&","&(A13)&","&(A14)&","&(A15)&","&(A16)&","&(A17)&","&(A18)&","&(A19)&","&(A20),LEN(A1)+LEN(A2)+LEN(A3)+LEN(A4)+LEN(A5)+LEN(A6)+LEN(A7)+LEN(A8)+LEN(A9)+LEN(A10)+LEN(A11)+LEN(A12)+LEN(A13)+LEN(A14)+LEN(A15)+LEN(A16)+LEN(A17)+LEN(A18)+LEN(A19)+LEN(A20)+COUNT(A1:A20)-1)

I hope this helps. Please advise as to how the formula fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DPlank]

Code is successful!!

Thanks!

 

[DPlank]

OK New question.

How do I do that for approximately 3000 cells?

That has worked fine for the first request, but I have a similar (larger scale) version.

Uh-oh methinks.

David

 

[KenWright]

You don't. You either use VBA or a different kind of formula.

Assuming your data is in A1:A3000, in cell B1 put =A1

In cell B2 put =B1&","&A2 and then just copy down to B3000 (Hover the mouse over the bottom right corner of the cell and double click).

Then select cell B3000, hit F2 then F9 and then enter

With 3000 entries you need to be careful that your average string length is not more than around 10 characters long, as you will run into problems with the amount of text the cell can hold. Max of 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

Regards
Ken................

 

[KenWright]

And if you want to use VBA, then use the following:-

Sub Concat()

Dim LastRw As Long
Dim Rng As Range
Dim str As String

str = Range("A1&quot.Value

LastRw = Cells(Rows.Count, "A&quot.End(xlUp).Row
Set Rng = Range(Cells(1, "A&quot, Cells(LastRw, "A&quot)

For x = 1 To Rng.Rows.Count
str = str & "," & Rng.Rows(x).Value
Next x

Range("B1&quot = str

End Sub

1) Hit ALT+F11 to get you into the VBE
2) Find your project name in the explorer style interface at the top left pane of the VBE
3) Right click on your project and select 'insert module'
4) Double click on module1 when it appears, and then click into the big white space on the right of your screen when it appears.
5) Paste the code supplied into it
6) Hit File / Close and return to Microsoft Excel
7) Save Workbook
8) Hit Tools / Macros / Macro / Concat

Regards
Ken...............

 

[DPlank]

Thanks for the VBA code. I'm using that so I don't have thousands of formulas where they're not required.

Can you perhaps help me expand the code so that it looks in another cell for a date?

My sheet looks something like this ::
A B C D E F
1 New ................ 18/06/02
2 Closed .............. 15/05/03
3 Open ................ 19/05/03
4 New ................. 12/06/03
5 New ................. 08/06/03


What I really need to do is select all the ID numbers (1-5 in the example), and concatenate them into a string separated by commas, where the relevant date is more than 5 days in the past, and where the cell shown has status New.

Currently I have a formula in the first 20 cells or so of column H which is :

=if(AND(B2 = &quot;New&quot;, F2 <(Today()-5)), A1, &quot;&quot;)

I'd like to do the whole thing without resorting to copied down formulae if possible.

Any help greatly appreciated...

 

[KenWright]

Obviously with the first method you aren't actually stuck with the formulas as you can delete them, but I'd still prefer VBA for what you want anyway. Try the following, and just change the "B1" reference for whatever cell you want the data to appear in.

Sub Concat2()

Dim LastRw As Long
Dim Rng As Range
Dim str As String

str = ""

LastRw = Cells(Rows.Count, "A&quot.End(xlUp).Row
Set Rng = Range(Cells(1, "A&quot, Cells(LastRw, "A&quot)

For x = 1 To Rng.Rows.Count
If Rng.Rows(x).Value = "New" And _
Rng.Rows(x).Offset(0, 5).Value <= (Date - 5) Then
str = str & &quot;,&quot; & Rng.Rows(x).Offset(0, 5).Value
End If
Next x

Range(&quot;B1&quot = Right(str, Len(str) - 1)

End Sub

Regards
Ken..............

 

[DPlank]

Thanks bud.

Have a star!