Multiple cells into 1 issue

[Newbienew]

I am attempting to add 4 cells into 1 with "+" in between the first three results and "=" at the end.

B6	C6	D6	E6	F6	G6	H6	I6	J6	M6 (FORMULA)
VE		AR		LO		DH		3	VE+AR+LO+DH=3
AR		HE		HU				5	AR+HE+HU=5
HE		IU						9	HE+IU=9

Cell M7 is where I started my formula. If all 4 cells are filled in the formula works great but anything less my formula bombs. The + and = are always left behind and looks like the following.
AR+HE+HU+0=5
HE+IU+0+0=9
or if all other cells are blank
+++=

Being each cell can vary in data, the formula would need to be dynamic in order to display what is in each cell with the right characters needed.


Current formula in use =IF(COUNTA(H7),B7&"+"&D7&"+"&F7&"+"&H7,IF(COUNTA(F7),B7&"+"&D7&"+"&F7,IF(COUNTA(F7),B7&"+&D10","")))&"="&K7

I even attempted to use concentrate, but I get the same results

What set of formulas will i need to make this happen.

 

[mohadin]

Hi
Try

=TEXTJOIN("+",1,B7:I7)&"="&J7

Or

=TEXTJOIN("=",TRUE,TEXTJOIN("+",1,B7:I7),J7)

 

[Newbienew]

I am not able to use Textjoin. I have Excel 2016 or rather that's what my the work computer has

 

[mohadin]

The Try

=IF(B6<>"",B6&"+","")&IF(C6<>"",C6&"+","")&IF(D6<>"",D6&"+","")&IF(E6<>"",E6&"+","")&IF(F6<>"",F6&"+","")&IF(G6<>"",G6&"+","")&IF(H6<>"",H6&"+","")&IF(I6<>"",I6&"+","")&"="&J6

 

[Newbienew]

I used your formula. I did remove the sections I didn't need to account for at this time C,E,G, and I. I will need the same formula for those cells as well but I can base it off the B,D,F,and H.
I used the original formula and the changed one, and I got the same results. The one change I did notice was when there is no data at all, the "=" is the only thing left behind.

 

[mohadin]

The formula should be placed in M6
The have to be dragged down

VE​		AR​		LO​		DH​		3​			VE+AR+LO+DH+=3​
AR​		HE​		HU​				5​			AR+HE+HU+=5​
HE​		IU​						9​			HE+IU+=9​

 

[mohadin]

M6=IF(B6<>"",B6&"+","")&IF(C6<>"",C6&"+","")&IF(D6<>"",D6&"+","")&IF(E6<>"",E6&"+","")&IF(F6<>"",F6&"+","")&IF(G6<>"",G6&"+","")&IF(H6<>"",H6&"+","")&IF(I6<>"",I6&"+","")&"="&J6
M7=IF(B7<>"",B7&"+","")&IF(C7<>"",C7&"+","")&IF(D7<>"",D7&"+","")&IF(E7<>"",E7&"+","")&IF(F7<>"",F7&"+","")&IF(G7<>"",G7&"+","")&IF(H7<>"",H7&"+","")&IF(I7<>"",I7&"+","")&"="&J7
M8=IF(B8<>"",B8&"+","")&IF(C8<>"",C8&"+","")&IF(D8<>"",D8&"+","")&IF(E8<>"",E8&"+","")&IF(F8<>"",F8&"+","")&IF(G8<>"",G8&"+","")&IF(H8<>"",H8&"+","")&IF(I8<>"",I8&"+","")&"="&J8

 

[Newbienew]

M6=IF(B6<>"",B6&"+","")&IF(C6<>"",C6&"+","")&IF(D6<>"",D6&"+","")&IF(E6<>"",E6&"+","")&IF(F6<>"",F6&"+","")&IF(G6<>"",G6&"+","")&IF(H6<>"",H6&"+","")&IF(I6<>"",I6&"+","")&"="&J6
M7=IF(B7<>"",B7&"+","")&IF(C7<>"",C7&"+","")&IF(D7<>"",D7&"+","")&IF(E7<>"",E7&"+","")&IF(F7<>"",F7&"+","")&IF(G7<>"",G7&"+","")&IF(H7<>"",H7&"+","")&IF(I7<>"",I7&"+","")&"="&J7
M8=IF(B8<>"",B8&"+","")&IF(C8<>"",C8&"+","")&IF(D8<>"",D8&"+","")&IF(E8<>"",E8&"+","")&IF(F8<>"",F8&"+","")&IF(G8<>"",G8&"+","")&IF(H8<>"",H8&"+","")&IF(I8<>"",I8&"+","")&"="&J8

These are my end results. the fist one is with no data enter.

VE+0+AR+0+MK+0+AS+0+=0

=

=

=

the second

VE+21+AR+25+MK+28+GS+25+=99

MK+28+GS+25+VE+21+0+0+=74

=

=

 

[Newbienew]

I must add my formula starts in cell M7. I thought adding M6 Formula would of came of cross that that is the column I was using. Sorry about that. The formula is based off the one in the first posting

 

[shinigamilight]

I coded a text join function for your version of excel which ignores blank. I have tested it thoroughly and it ignores all the blank
Press Alt + F11 on your keyboard and click on insert and then click on module and paste this code in it. Then when you will type =textjoin a function will popup.
The syntax is range, delimiter of your choice.
M6: =TEXTJOIN(B6:I6,"+")&"="&J6

Function TEXTJOIN(rng As Range, Delim As String)
            Dim store As Long, rng2 As Range, V, S
            Dim i As Integer
          
            If rng.Rows.Count = 1 Then
            ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Column Loop
            ReDim V(1 To rng.Columns.Count)
          
            For Each rng2 In rng
                    If rng2 <> "" Then
                      i = i + 1
                      V(i) = rng2.Value
                    End If
            Next rng2
          
            For i = LBound(V) To UBound(V)
                If V(i) <> "" Then
                    store = store + 1
                End If
            Next i
          
            ReDim S(1 To store)
          
            store = 0
            For i = LBound(V) To UBound(V)
                If V(i) <> "" Then
                    store = store + 1
                    S(store) = V(i)
                 End If
            Next i
          
            TEXTJOIN = Join(S, Delim)
            ' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Row Loop
            Else
          
            ReDim V(1 To rng.Rows.Count)
          
            For Each rng2 In rng
                    If rng2 <> "" Then
                      i = i + 1
                      V(i) = rng2.Value
                    End If
            Next rng2
          
            For i = LBound(V) To UBound(V)
                If V(i) <> "" Then
                    store = store + 1
                End If
            Next i
          
            ReDim S(1 To store)
          
            store = 0
            For i = LBound(V) To UBound(V)
                If V(i) <> "" Then
                    store = store + 1
                    S(store) = V(i)
                 End If
            Next i
          
            TEXTJOIN = Join(S, Delim)
          
            End If

End Function