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 column to text with delimiter in Excel.


Download Practice Workbook

You can download the practice workbook here.


5 Easy Ways to Convert Column to Text with Delimiter in Excel

In this article, we will discuss how to convert to text with delimiter in Excel in 5 different ways. Firstly, we will use the TEXTJOIN function. Secondly, we will go for the CONCAT function. Thirdly, we will resort to a VBA code. Fourthly, we will utilize the Ampersand operator to do so. Finally, we will use a combination of the CONCATENATE and TRANSPOSE functions to convert a column to text with delimiters.


1. Applying TEXTJOIN Function

The TEXTJOIN function combines values from a range or ranges and separates them with a delimiter. In this method, we will combine values from a column with commas as the delimiter using the function.

Steps:

  • To start with, click on the C12 cell and type the following,
=TEXTJOIN(",",TRUE,B5:B10)
  • Then, hit Enter.

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

  • Consequently, all the values in the column will be arranged in a horizontal text with a comma in between them.

Read More: How to Convert Text to Columns Without Overwriting in Excel


2. Using CONCAT Function

The CONCAT function combines two or more texts together. In this method, we will use this function to combine texts with a delimiter in between them. We will use commas and semi-colons as delimiters.

Steps:

  • Firstly, select the C10 cell and enter the following,
=CONCAT(B5:C8)
  • Press Enter.

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

  • As a result, we will get the values separated by delimiters.

Read More: How to Use Text to Columns Feature with Carriage Return in Excel


3. Applying VBA Code

VBA allows a multi-stepped task to be done with a few lines of code. In this method, we will use the power of VBA to convert a column to text with delimiters in Excel. We will write a simple VBA code.

Steps:

  • Firstly, go to the Developer tab in the ribbon.
  • From there, select the Visual Basic tab.
  • Consequently, the Visual Basic window will be opened.

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

  • After that, in the Visual Basic tab, click on Insert.
  • Then, select the Module option.
  • Consequently, a coding module will appear.

  • After that, write the code in the module and save it.

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

  • Finally, run the code by clicking on the green triangular sign.

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

  • Consequently, we will get the text with delimiters.

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


4. Applying Ampersand Operator

The Ampersand operator (“&”) is the easiest way to combine texts. In this illustration, we will use this operator. However, this method will be applicable if the data is small. For a big dataset this method will be a liability because it will take a lot of time and effort.

Steps:

  • Firstly, select the C10 cell and write down the following,
=B5&","&B6&","&B7&","&B8
  • Then, hit the Enter button.

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

  • Consequently, we will get our desired text with delimiters.

Read More: [Fixed!] Excel Text to Columns Is Deleting Data


5. Using a Combination of CONCATENATE and TRANSPOSE Functions

The CONCATENATE function combines two or more texts. The TRANSPOSE function rotates the cell data. In this example, we will combine these two functions to create a text with delimiters.

Steps:

  • To begin with, choose the C12 cell and type,
=CONCATENATE(TRANSPOSE(B5:B10)&“,”)

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

  • Then, 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

  • Consequently, we will get a horizontal list inside the formula.
  • Remove the {“signs within the formula.
  • Then, hit Enter.

  • As a result, we will get the text with delimiters.

How to Separate Text in Excel

We often need to separate texts to represent them properly. In this method, we will use the Excel Flash Fill feature to separate the texts.

Steps:

  • Firstly, write the first text before the comma in the B5 cell in the C5 cell.

  • Then, press Ctrl+Enter.

  • Finally, press Ctrl+E to flash fill the rest of the cells.

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


How to Split Text in Excel Using Formula

The LEFT function separates a portion of a text from the left side of the text. The FIND function finds out the position of a certain letter or symbol within a text. In this method, we will combine these two functions split a text.

Steps:

  • Choose the C5 cell and type the following,
=LEFT(B5,FIND("-",B5)-1)
  • Then, press the Enter button.

  • Consequently, we will get a split portion of the whole text.
  • Finally, lower the cursor to autofill the rest of the cells.

Read More: How to Split Text to Columns Automatically with Formula in Excel


Conclusion

In this article, we have discussed 5 effective ways how to convert column to text with delimiter in Excel. This will allow users to present their data with a delimiter in between them. It will also help them to display their data in a horizontal format.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo