Combining cell values

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Dear All

Would like some help on putting together some code please.

How can I join a set of values in cells in a column into 1 long string in a cell?
For example, my problem is - starting at H3 (H2 being a heading) I have a list of outstanding invoices (i.e. H3 = 70k H4 = 228k etc down to H?) and I would like to join these together separated by a comma into 1 string say in H1 (i.e. 70k, 228k, etc, etc, etc) to put into a report.

I have very basic knowledge of using arrays and examples I have found do not do what I need.

Thanks as ever.
Julian
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi - You could use the Concatenate function (help topic available in Excel)

=concatenate(a1,a2,a3,a4,a5) etc

HTH
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
in a cell, simply put:

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

where there are values in cells A1 through to D1

That should do it...

HTH
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Hi Mark/Patrick

Thanks for that, I do know about concatenate but due to the number of invoices and that it changes each period I was looking to use VBA to automate.

Cheers
Julian
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,861
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Mark/Patrick

Thanks for that, I do know about concatenate but due to the number of invoices and that it changes each period I was looking to use VBA to automate.

Cheers
Julian
Something like this?<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ConcatenateInvoices()
    <SPAN style="color:#00007F">Dim</SPAN> Result<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    
    LastRow = Range("H65536").End(xlUp).Row
    <SPAN style="color:#00007F">If</SPAN> LastRow< 3<SPAN style="color:#00007F">Then</SPAN>
        Range("H1").ClearContents
        <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    Result = Cells(3, 8).Value
    <SPAN style="color:#00007F">If</SPAN> LastRow > 3<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> r = 4<SPAN style="color:#00007F">To</SPAN> LastRow
            <SPAN style="color:#00007F">If</SPAN> Cells(r, 8).Value<> ""<SPAN style="color:#00007F">Then</SPAN>
                Result = Result & ", " & Cells(r, 8).Value
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> r
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    Range("H1").Value = Result<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Here is the result for my data:
Mr Excel.xls
FGHI
170k, 256b, 45r, 345fg, 12b
2Invoices
370k
4256b
545r
6345fg
7
8
912b
10
Concatenate
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87

ADVERTISEMENT

Hi Peter

Only 1 small thing.

Is it possible to have the last comma as a & instead?
ie 12k, 156k, 128k & 220k

Cheers
Julian
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,861
Office Version
  1. 365
Platform
  1. Windows
Julian

Not sure this is the 'neatest' way, but it is what I have come up with. Give it a try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ConcatenateInvoices()
    <SPAN style="color:#00007F">Dim</SPAN> Result <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastComma <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    LastRow = Range("H65536").End(xlUp).Row
    <SPAN style="color:#00007F">If</SPAN> LastRow < 3 <SPAN style="color:#00007F">Then</SPAN>
        Range("H1").ClearContents
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Result = Cells(3, 8).Value
    <SPAN style="color:#00007F">If</SPAN> LastRow > 3 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> r = 4 <SPAN style="color:#00007F">To</SPAN> LastRow
            <SPAN style="color:#00007F">If</SPAN> Cells(r, 8).Value <> "" <SPAN style="color:#00007F">Then</SPAN>
                Result = Result & ", " & Cells(r, 8).Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> r
       LastComma = InStrRev(Result, ",")
       Result = Left(Result, LastComma - 1) & " &" & Right(Result, Len(Result) - LastComma)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Range("H1").Value = Result
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Hi Peter

Yes had a quick go and all looks good.
I have been playing and have customised it but a big thanks for getting me started.

Thanks a million for all your help.

Best regards
Julian
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,685
Members
410,697
Latest member
srishtijain0708
Top