Adnan Masruf
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.
- Consequently, all the values in the column will be arranged in a horizontal text with a comma in between them.
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.
- As a result, we will get the values separated by delimiters.
Read More: How to Use Line Break as Delimiter in Excel Text to Columns
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.
- 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.
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.
- Consequently, we will get the text with delimiters.
Similar Readings
- How to Use Text to Columns Feature with Carriage Return in Excel
- How to Convert Text to Columns Without Overwriting in Excel
- [Fixed!] Excel Text to Columns Is Deleting Data
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.
- Consequently, we will get our desired text with delimiters.
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)&“,”)
- Then, select the “TRANSPOSE(B5:B10) “,”” portion of the formula and press F9.
- 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.
Read More: How to Convert Text to Columns in Excel with Multiple Spaces
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.
