Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #468 www.energyefficientbuild.com
user cowboy713 said:
Hi everyone, my names Bill. I'm attempting to concatenate multiple large ranges of cells for a project I'm working on. I got my function down to =ArrayFormula(CONCATENATE(U4:W33&", ")) which is working great and pulling any data in those cells into a new one for me, however some of the cells included in that range are empty. Is there any way to change this function so that empty cells are not included in the concatenation? Right now my data's coming out as ",,,,,Value 1, Value 2,,,,,,,,,, Value3,,,Value 4,,, Value 5". Obviously I'd like to avoid this :-) Thanks! UPDATE user cowboy713 said: Nevermind, figured out a VB macro to include an if statement to check for length. If anyone's curious I added it below. Function ConcatenateRange(myRange, Separator) FirstCell = True myRangeValues = myRange.Value For Each thecell In myRangeValues If FirstCell Then ConcatenateRange = thecell Else If Len(thecell) > 0 Then ConcatenateRange = ConcatenateRange & Separator & thecell Else End If End If FirstCell = False Next End Function
-----------------------------------------
Well, following is a formula based solution to the problem that can do what needs to be done without using a macro be it it a VBA macro as suggested by cowboy713 for Excel or a macro developed using Google Apps Script. So, here we go ...
user cowboy713 said:
Hi everyone, my names Bill. I'm attempting to concatenate multiple large ranges of cells for a project I'm working on. I got my function down to =ArrayFormula(CONCATENATE(U4:W33&", ")) which is working great and pulling any data in those cells into a new one for me, however some of the cells included in that range are empty. Is there any way to change this function so that empty cells are not included in the concatenation? Right now my data's coming out as ",,,,,Value 1, Value 2,,,,,,,,,, Value3,,,Value 4,,, Value 5". Obviously I'd like to avoid this :-) Thanks! UPDATE user cowboy713 said: Nevermind, figured out a VB macro to include an if statement to check for length. If anyone's curious I added it below. Function ConcatenateRange(myRange, Separator) FirstCell = True myRangeValues = myRange.Value For Each thecell In myRangeValues If FirstCell Then ConcatenateRange = thecell Else If Len(thecell) > 0 Then ConcatenateRange = ConcatenateRange & Separator & thecell Else End If End If FirstCell = False Next End Function
-----------------------------------------
Well, following is a formula based solution to the problem that can do what needs to be done without using a macro be it it a VBA macro as suggested by cowboy713 for Excel or a macro developed using Google Apps Script. So, here we go ...
No comments:
Post a Comment