Forum Discussion
Can I get this done in Excel or use other commands?
Yes you can with TEXTJOIN function.
lets say your data is in range A1 to A16 then use the below formula.
=TEXTJOIN("|",TRUE,A1:A16)
If you do not have Office365 then you can create the function by placing this VBA code below in a module.
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant) For Each cellrng In cell_ar For Each cell In cellrng If ignore_empty = False Then result = result & cell & delimiter Else If cell <> "" Then result = result & cell & delimiter End If End If Next cell Next cellrng TEXTJOIN = Left(result, Len(result) - Len(delimiter)) End Function
Yes you can with TEXTJOIN function.
lets say your data is in range A1 to A16 then use the below formula.
=TEXTJOIN("|",TRUE,A1:A16)
If you do not have Office365 then you can create the function by placing this VBA code below in a module.
Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant) For Each cellrng In cell_ar For Each cell In cellrng If ignore_empty = False Then result = result & cell & delimiter Else If cell <> "" Then result = result & cell & delimiter End If End If Next cell Next cellrng TEXTJOIN = Left(result, Len(result) - Len(delimiter)) End Function
Thank you very much. It works
- Did you use the textjoin of office365 or did you use the UDF?
Never mind the previous reply. I figure it out. It works great just as Office 365.
Thanks,
I tested textjoin of office365.
Actually, I don't know how to use the UDF. My office365 can only be installed on 5 computers. I have other computers only have office 2016 pro.
It will be greatly helpful if you could be more specific.