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.

 

[shinigamilight]

A shorter version of above code.

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
            ReDim V(1 To rng.Columns.Count)
            Else: ReDim V(1 To rng.Rows.Count)
            End If
            
            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 Function

 

[Newbienew]

I love this and will us it for my personal version, but this is going to have to be a non-macro. We use an in house online share folder. Marcos are disabled and will not work. I thought I could get a way with it doing something else.

 

[Newbienew]

Replying back to this thread as I have been fiddling with this for some time now and I had part of it figured out but not the rest. I am not sure what to do or what I am doing wrong.

My first result needs to be the letters and + sign with the sum as displayed in the first red cell. But depending on what is chosen I get VE+VE+=145 or VE+VE+0+0=
The same thing goes for the second red cell with the numbers.

I believe that this is due to the these cells having formula's in them

If the source cell returns VE 34 GS 56 MK 34 I need the first read cell to be VE+GS+MK= but if the source cell changes and it becomes VE 52 GS 75 It just needs to display that and not VE+GS+0+0= or VE+GS+0= it just needs to be VE+GS

This goes for the second red cell with the numbers.

It work PERFECT when all cells are filled. But when not I get this situation I have. I have attached a workbook with the unlocked cells needed. I have added 3 selections that duplicate the issue at hand.

Please help Character Display issue workbook

 

[Newbienew]

Any help with for 2016 excel version formula would be greatly appreciated

 

[Newbienew]

Help, Please.