If you want to take text from multiple columns and merge it into one, you can easily do it in Excel.
There are a few ways to do it, using an ampersand (&), the CONCAT function, or VBA.
In this lesson, I’m going to show you all these methods.
First, let’s take a look at the example.
“This is plain text” the message says. We want to take all these cells from different columns and merge them into one cell.
Columns to text with an ampersand
Let’s start with an ampersand. Enter the following formula into a cell.
|
1 |
=A1&" "&B1&" "&C1&" "&D1&E1 |
This is how it looks like.
Columns to text with a function
The CONCAT function (formerly CONCATENATE) will merge multiple cells into a single one.
You can select all cells at once, but there will be no separation between values.
|
1 |
=CONCAT(A1:E1) |
This formula will return this text.
|
1 |
"Thisisplaintext." |
For this reason, you have to add a separation between each word.
|
1 |
=CONCAT(A1," ",B1," ",C1," ",D1,E1) |
And now, we have text separated by spaces.
Columns to text with VBA
This one is going to make our work much easier. But first, we have to create a VBA function.
Press Left Alt + F11 to open VBA for application.
Insert a new module and enter this function.
|
1 2 3 4 5 6 |
Public Function ColumnsToText(rng As Range, Optional delimiter As String = " ") As String Dim var As Variant var = Application.Transpose(Application.Transpose(rng.Value)) ColumnsToText = Join(var, delimiter) End Function |
Let’s use this function. Enter a range as an only argument.
|
1 |
=ColumnsToText(A1:E1) |
And the effect will be similar to the previous examples.
|
1 |
"This is plain text ." |
You can also add a delimiter to change the default one.
|
1 |
"This-is-plain-text-." |
The problem with this function is that there is no way to remove the delimiter before a dot.
Let’s modify this function by adding the third optional parameter and conditional statements.
|
1 2 3 4 5 6 7 8 9 10 |
Public Function ColumnsToText(rng As Range, Optional delimiter As String = " ", Optional pMark As Boolean = False) As String Dim var As Variant var = Application.Transpose(Application.Transpose(rng.Value)) replaceStr = Join(var, delimiter) If pMark = TRUE Then ColumnsToText = StrReverse(Replace(StrReverse(replaceStr), StrReverse(delimiter), StrReverse(""), , 1)) Else ColumnsToText = replaceStr End If End Function |
The third argument, which is by default FALSE will indicate whether there is a punctuation mark at the end, if there is, the last delimiter is removed.
This is how to execute this function.
|
1 2 3 |
=ColumnsToText(A1:E1) =ColumnsToText(A1:E1, " ") =ColumnsToText(A1:E1, " ", FALSE) |
All of them return the same result.
|
1 |
"This is plain text ." |
Let’s try the next one.
|
1 |
=ColumnsToText(A1:E1, " ", TRUE) |
This one returns the result we want.
|
1 |
"This is plain text." |