Ignore blank cells on formula to join text from different cells

bmp_costa

New Member
Joined
Apr 10, 2013
Messages
6
Hi everyone, I've got the following formula and can't find a way to ignore the blank cells if not data is entered. I'm trying to avoid having double comas when no data is in one of the cells. using formula (=CONCATENATE(A1, ", ",B1, ", ",C1)
Column 1
Column 2
Column 3
Formula result
One
Two
Three
One, Two, Three
Three
, , Three

<tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the board.

Are you comfortable using a UDF? If so, please see the below...

Sheet1

*ABCD
1OneTwoThreeOne,Two,Three
2**ThreeThree

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:33px;"><col style="width:64px;"><col style="width:110px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D1=Concatenate_Range(A1:C1,",")
D2=Concatenate_Range(A2:C2,",")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Code:
Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
Dim Cell As Range


Application.Volatile


For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & myDelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell


If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))


End Function
 
Upvote 0
If you dislike macros:

=A1 & IF(AND(A1<>"",B1 & C1<>""),", ","") & B1 & IF(AND(B1<>"",C1<>""),", ","") & C1
 
Upvote 0
Thanks Neil, that worked perfectly. Unfortunately I've just realised that I don't need a range i.e. (A1:C1) but only certain cells to be considered. My knowledge is good enough to use the formula and code you sent but not enough to figure this out for myself.
ABCDE
1OneTwoblahThreeformula to join text only from A1, C1 and E1, ignoring blank and other cells even if these contain text as in D1. Result=One, Two, Three
2
3

<tbody>
</tbody>
 
Upvote 0
Well the code I posted will ignore blanks, but what are the rules for other text strings you want ignored? If you only want the string equivalent of numbers ("one","two","three",etc) to be returned, what's the largest number you would expect?
 
Upvote 0
Thanks Gary's Student, that works as well and it may save Neil some work with a new code. I can tweak this to do what I need. Thanks again
 
Upvote 0
Hi Neil, thanks for the reply. Your code works fine for a range. There isn't actually a rule for the cells I want ignored and I have used "one", "two", "three" just as an example. the text can be just anything. I appreciate your help and I must say the code you sent first works fine for my initial question, but I will use the formula from Gary's Student as it fits what I need to do. Thanks so much for your help guys.
Well the code I posted will ignore blanks, but what are the rules for other text strings you want ignored? If you only want the string equivalent of numbers ("one","two","three",etc) to be returned, what's the largest number you would expect?
 
Upvote 0
Thank you both, I know its messy but got it working with the following:=A1 & IF(AND(A1<>"",C1 & E1<>""),",","") & C1 & IF(AND(C1<>"",E1<>""),",","") & E1& IF(AND(E1<>"",H1<>""),",","") & H1& IF(AND(H1<>"",J1<>""),",","") & J1& IF(AND(J1<>"",L1<>""),",","") & L1& IF(AND(L1<>"",O1<>""),",","") & O1& IF(AND(O1<>"",Q1<>""),",","") & Q1& IF(AND(Q1<>"",S1<>""),",","") & S1& IF(AND(S1<>"",V1<>""),",","") & V1& IF(AND(V1<>"",X1<>""),",","") & X1& IF(AND(X1<>"",Z1<>""),",","") & Z1
 
Upvote 0
If you want to keep it semi-simple and don't feel like making a UDF, ...recording macros, or self-writing any VBA, here is a elegant & scalable vanilla-formula.

=SUBSTITUTE(TRIM(JOIN(" ",{your range}))," ",", ")

1. The inner JOIN function works well by itself if you have no blanks, but when you do it still uses a delimiter, causing duplicates. In my example I used just white-space " " as a delimiter so that I can utilize the...
2. TRIM function to eliminate all excess spaces (defined as: a space can not be a preceding or ending character, but only space can be used as a deliminiter between non-space characters.
3. Finally use the SUBSTITUTE formula to convert all space " " delimiters back into comma-space delimiters ", "
4. Profit.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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