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

Posted on Oct 31, 2022Jun 16, 2024 by Adnan Masruf

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.

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:

=CONCAT(B5:C8)

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:

=B5&","&B6&","&B7&","&B8

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:

=CONCATENATE(TRANSPOSE(B5:B10)&“,”)

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:

=LEFT(B5,FIND("-",B5)-1)

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.

Convert Column to Text with Delimiter.xlsm

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Leave a Reply Cancel reply