Easiest Way To Concatenate

[Dazzawm]

I have a sheet as below with several dozen columns and thousands of rows. I want to join each column separtated by a / and a space. They are set out like below but if i do a normal concatenate formula I could end up with row 2 something like 8E0145806Q/ / / / / /. Row 4 is what how I would like it like this 4475010301/ 4475011301/ A4475010301/ A4475011301

Thanks

Excel 2010
	A	B	C	D	E	F	G
1	Number	Other Numbers	Other Numbers	Other Numbers	Other Numbers	Other Numbers	Other Numbers
2	12345	8E0145806Q
3	12346	03C 145 749 B	03F145749C
4	12347	4475010301	4475011301	A4475010301	A4475011301
5	12348	4851182	4967772	8C169L440BB	8C169L440BA
6	12349	144614EB0A	144614EB1A	144614EB1B
7	12350	95B145804A	95B145804B
8	12351	95B145803A
9	12352	51819186
10	12353	51819187
11	12354	99611063971
12	12355	99611064071
13	12356	95511063901	7L5145803B	95511063900
14	12357	504344505	504235460	5801349168
15	12358	144614BE0A
16	12359	5801255844	5801255806	5801526779
17	12360	5801255907	5801526777	5802036825
18	12361	6395011301	A6395011301
19	12362	95511064001	7L5145804B	95511064000
20	96308	144961489R	4150900014	4150900114	A4150900114	144963234R	A4150900014
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Sheet3<br />

 

[tyija1995]

Hey, if you have TEXTJOIN built in then use that as follows:

=TEXTJOIN("/ ",TRUE,*range of cells here*)

But it looks as if you're using Excel 2010, I don't think that has textjoin, but I found online a UDF that replicates TEXTJOIN and can be used for older versions:

Function TEXTJOIN(delim As String, ie As Boolean, ParamArray arguments() As Variant) As Variant
  'https://www.reddit.com/u/excelevator
  'https://old.reddit.com/r/excelevator
  'https://www.reddit.com/r/excel - for all your Spreadsheet questions!
'TEXTJOIN( delimiter , ignore_empty , "value"/range, ["value"/range]..)
'See Microsoft TEXTJOIN Helpfile
Dim tmpStr As String 'build cell contents for conversion to array
Dim argType As String, uB As Double, arg As Double, cell As Variant
uB = UBound(arguments)
For arg = 0 To uB
argType = TypeName(arguments(arg))
If argType = "Range" Or argType = "Variant()" Then
    For Each cell In arguments(arg)
        If ie = True And cell = "" Then
            'do nothing
        Else
            tmpStr = tmpStr + CStr(cell) + delim
        End If
    Next
Else
    If ie = True And CStr(arguments(arg)) = "" Then
        'do nothing
    Else
        tmpStr = tmpStr + CStr(arguments(arg)) + delim
    End If
End If
Next
If argType = "Error" Then
    TEXTJOIN = CVErr(xlErrNA)
Else
    tmpStr = IIf(tmpStr = "", " ", tmpStr) 'fill for no values to avoid error below
    TEXTJOIN = Left(tmpStr, Len(tmpStr) - Len(delim))
End If
End Function

 

[Dazzawm]

Thanks, probably doing it wrong but just get results of #VALUE !

 

[Dazzawm]

I was doing it wrong, seems to do what I need. Thanks.