How to Convert Column to Text with Delimiter in Excel

We often need a list in a column in an Excel sheet to arrange in a horizontal orientation. We will need a delimiter to separate the texts in that horizontal arrangement. We often use commas as delimiters. Excel does not provide a direct way to arrange the column into text separated by a delimiter. In this article, we will show how to convert columns to text with a delimiter in Excel.

How to Convert Column to Text with Delimiter in Excel: 5 Methods

Method 1 – Applying the TEXTJOIN Function to Convert Column to Text with Delimiter

The TEXTJOIN function combines values from a range or ranges and separates them with a delimiter.

Steps:

Select cell C12 and enter the following:

=TEXTJOIN(",",TRUE,B5:B10)

Press Enter.

All the values in the column will be arranged horizontally and separated by a comma.

Method 2 – Converting Text to Column with Delimiter Using the CONCAT Function

Steps:

Select cell C10 and enter the following:

=CONCAT(B5:C8)

Press Enter.

You will see the values separated by delimiters.

Method 3 – Applying VBA Code

Steps:

Go to the Developer tab.
Select the Visual Basic tab to open the window.

In the Visual Basic tab, click Insert.
Select the Module option.
A coding module will appear.

Enter the following code in the module and save.

Sub ColumnToText()
Dim a As Integer
Dim b As String
a = 5
Do Until Cells(a, 2).Value = ""
    If (b = "") Then
        b = Cells(a, 2).Value
    Else
        b = b & "," & Cells(a, 2).Value
    End If
    a = a + 1
Loop    
Cells(10, 3).Value = b
End Sub