Concatenate Cell Range With Blanks

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello,

Looking for a formula that will Concatenate a range of cell values that will keep the values intact.
For example,
Cells:
A1 = 0000
A2 = 777
A3 = 333
A4 = 222
A5 = 99
A6 = 88
A7 = 9
A8 = 8
A9:A20 are blank but could contain values.

Concatenate Cells A1:A20
B1 = 0000, 777, 333, 222, 99, 88, 9, 8

Thank you
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Here's a UDF you can try:
Book1.xlsm
AB
100000000, 777, 333, 222, 99, 88, 9, 8
2777
3333
4222
599
688
79
88
Sheet3
Cell Formulas
RangeFormula
B1B1=EasyConcat(A1:A8,", ")

VBA Code:
Function EasyConcat(R As Range, Optional Delim As String)
'Concatenates contents of contiguous cells in a single column or a single row
If R.Count < 2 Or R.Areas.Count > 1 Then
    EasyConcat = CVErr(xlErrNA)
    Exit Function
End If
If R.Rows.Count = 1 Then
    EasyConcat = Join(Application.Index(R.Value, 1, 0), Delim)
    Exit Function
ElseIf R.Columns.Count = 1 Then
    EasyConcat = Join(Application.Transpose(R.Value), Delim)
    Exit Function
End If
'more than 1 row or 1 column in R
EasyConcat = CVErr(xlErrNA)
End Function
 
Upvote 0
Joe,
Thanks for the code. Works great!!
Does it really work correctly for you? Your original question said your range could be A1:A20 with A9:A20 being blank. If you pass the range A1:A20 to Joe's function using the posted values and specify a comma as the delimiter, the function will return a bunch of trailing commas. Is that really what you wanted? If not, then consider the following modification of Joe's code which eliminates those excess delimiters.
VBA Code:
Function EasyConcat(R As Range, Optional Delim As String)
  Dim Arr As Variant
  'Concatenates contents of contiguous cells in a single column or a single row
  If R.Count < 2 Or R.Areas.Count > 1 Or Not (R.Rows.Count > 1 Xor R.Columns.Count > 1) Then
    EasyConcat = CVErr(xlErrNA)
    Exit Function
  End If
  Arr = Evaluate(Replace("IF(@="""","""",SUBSTITUTE(@,"" "",CHAR(1)))", "@", R.Address))
  If R.Rows.Count = 1 Then
    EasyConcat = Join(Application.Index(Arr, 1, 0))
  ElseIf R.Columns.Count = 1 Then
    EasyConcat = Join(Application.Transpose(Arr))
  End If
  EasyConcat = Replace(Replace(Application.Trim(EasyConcat), " ", Delim), Chr(1), " ")
End Function
 
Last edited:
Upvote 0
Thanks Rick,
Your correct in that Joe's code did return trailing commas from blank cells. The code does accomplish what I needed. I just removed the comma from the formula and used blanks.
Thanks for modifying the code.
 
Upvote 0
Does it really work correctly for you? Your original question said your range could be A1:A20 with A9:A20 being blank. If you pass the range A1:A20 to Joe's function using the posted values and specify a comma as the delimiter, the function will return a bunch of trailing commas. Is that really what you wanted? If not, then consider the following modification of Joe's code which eliminates those excess delimiters.
Good catch Rick, and an elegant fix! :)
 
Upvote 0
I do have a question which relates to custom VBA Functions
I use Excel 2010 and I'm not sure if happens to newer version of Excel.
When I reopen the Excel file with a custom function the cell(s) with the formula is blank?
Thanks in advance
 
Upvote 0
One other thing I forgot to add is that I copied the VBA code into a New Module and then saved the file as .xlsm.
I have come across other custom Functions which will not display the calculations in the cells.
If I click inside the cell and press enter the custom Function will calculate but I would need to do this with every cell that contains the custom Function.
Thank you
 
Upvote 0
One other thing I forgot to add is that I copied the VBA code into a New Module and then saved the file as .xlsm.
I have come across other custom Functions which will not display the calculations in the cells.
If I click inside the cell and press enter the custom Function will calculate but I would need to do this with every cell that contains the custom Function.
Thank you
I've not seen that behavior in Excel 2010 or any other version. Have you looked at your Trust Center settings, especially Macros and Trusted Locations?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top