Easiest Way To Concatenate

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1NumberOther NumbersOther NumbersOther NumbersOther NumbersOther NumbersOther Numbers
2123458E0145806Q
31234603C 145 749 B03F145749C
41234744750103014475011301A4475010301A4475011301
512348485118249677728C169L440BB8C169L440BA
612349144614EB0A144614EB1A144614EB1B
71235095B145804A95B145804B
81235195B145803A
91235251819186
101235351819187
111235499611063971
121235599611064071
1312356955110639017L5145803B95511063900
14123575043445055042354605801349168
1512358144614BE0A
1612359580125584458012558065801526779
1712360580125590758015267775802036825
18123616395011301A6395011301
1912362955110640017L5145804B95511064000
2096308144961489R41509000144150900114A4150900114144963234RA4150900014
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 />
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:

Code:
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
 
Upvote 0
Thanks, probably doing it wrong but just get results of #VALUE !
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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