Concatenate Cell Range With Blanks

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
Joe,
Thanks for the code. Works great!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,428
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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! :)
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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
 

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 2010
Platform
  1. Windows
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,358
Messages
5,547,464
Members
410,795
Latest member
santa12345
Top