Forum Discussion
Solved
Can I get this done in Excel or use other commands?
I have a few numbers. I want to get them together with a "|" to separate them. I want to get the result as the screenshot below. Is this possible? Many Thanks
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
7 Replies
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
Hi,
If you are on Office365 subscription the easiest way is to us TEXTJOIN like
=TEXTJOIN("|",TRUE,A1:A10)Thank you very much. It works
