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:

• Press Enter.

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

Read More: How to Convert Text to Columns with Multiple Delimiters in Excel

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

Steps:

• Select cell C10 and enter the following:

• Press Enter.

• You will see the values separated by delimiters.

Read More: How to Convert Text to Columns in Excel with Multiple Spaces

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

• Run the code by clicking on the green triangle.

• You will see the text with delimiters.

Method 4 – Applying Ampersand Operator

Steps:

• Select cell C10 and enter the following:

• Press Enter.

• You will see your text with delimiters.

Method 5 – Using a Combination of the CONCATENATE and TRANSPOSE Functions

Steps:

• Select cell C12 and enter the following:

• Select the “TRANSPOSE(B5:B10) “,”” portion of the formula and press F9.

• You will get a horizontal list inside the formula.
• Remove the “{“ signs within the formula.
• Press Enter.

• You will see the text with delimiters.

How to Separate Text in Excel

Steps:

• Enter the text before the comma from cell B5 in cell C5.

• Press Ctrl+Enter.

• Press Ctrl+E to flash-fill the rest of the cells.

• Repeat the same process to fill cells D5:D10.

How to Split Text in Excel Using Formula

Steps:

• Select cell C5 and enter the following:

• Press Enter.

• You will get a split portion of the whole text.
• Drag the cursor down to autofill the rest of the cells.

Read More: How to Use Line Break as Delimiter in Excel Text to Columns

Download Practice Workbook

You can download the practice workbook here.

Related Articles

• How to Convert Text to Columns in Excel
• How to Convert Text to Columns Without Overwriting in Excel
• [Fixed!] Excel Text to Columns Is Deleting Data
• How to Undo Text to Columns in Excel
• Excel Text to Columns Not Working

<< Go Back to Splitting Text | Split in Excel | Learn Excel