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.

inserting textjoin function to show how to convert column to text with delimiter in excel

  • 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.

typing cotcat function to show how to convert column to text with delimiter in excel

  • 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.

opening visual basic module to show how to convert column to text with delimiter in excel

  • 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.

writing vba code and save it to show how to convert column to text with delimiter in excel

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.

running vba code to show how to convert column to text with delimiter in excel

  • 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.

using ampersand operator to show how to convert column to text with delimiter in excel

  • 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)&“,”)

combining concatenate and transpose functions to show how to convert column to text with delimiter in excel

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

modifying formula using f9 to show how to convert column to text with delimiter in excel

  • 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.


Related Articles


<< Go Back to Splitting TextSplit in ExcelLearn Excel

Get FREE Advanced Excel Exercises with Solutions!
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo