This post explains that how to combine text from two or more cells into one cell in excel. How to concatenate the text from different cells into one cell with excel formula in excel. How to join text from two or more cells into one cell using ampersand symbol. How to combine the text using the TEXTJOIN function in excel 2016.

1. Combine text using Ampersand symbol

If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol. For example, if you want to combine texts in the Range B1:B3 into Cell C1, Just refer to the following steps:

1# Click Cell C1 that you want to put the combined text.

2# type the below formula in the formula box of C1.

=B1&B2&B3

3# press Enter, you will see that the text content in B1:B3 will be joined into C1.

Note:

If you want to add a space between the combined text, then you can use &” ” & instead of & in the above formula.

And if you want to add a comma to the combined text, just type &”,”& between the combined cells in the above formula.

If you want to add Line break between the combined cells using Ampersand operator, you should combine with the CHAR function to get the line break. Using the following formula:

=B1 & CHAR(10) & B2 & CHAR(10) & B3

2. Combine text using CONCATENATE function

If you want to join the text from multiple cells into one cell, you also can use the CONCATENATE function instead of Ampersand operator. Just following the below steps to join combine the text from B1:B3 into one Cell C1.

1# Select the cell in which you want to put the combined text. (Select Cell C1)

2# type the following formula in Cell C1.

=CONCATENATE(B1,B2,B3)

3# press Enter to complete the formula.

Note:  If you want to add a space between the combined text strings, you can add a space character (“ “) enclosed in quotation marks. Just like the below formula:

=CONCATENATE(B1," ",B2," ",B3)

If you want to add line break into the combined text string, you can use the CHAR function within the CONCATENATE function, just use the below formula:

=CONCATENATE(B1,CHAR(10),B2,CHAR(10),B3)

3. Combine text using TEXTJOIN function

If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells, it is only available in EXCEL 2016.  It can join the text from two or more text strings or multiple ranges into one string and also can specify a delimiter between the combined text strings. Just like the below formula:

=TEXTJOIN(" ",TRUE,B1:B3)

4. Combine text using VBA Code

Lastly, we’ll delve into the fourth method, utilizing VBA code to dynamically combine text from multiple cells. This method offers advanced customization and automation for merging text strings based on specific criteria.”

Press ‘Alt + F11‘ to open the Visual Basic for Applications (VBA) editor.

Right-click on any item in the project explorer, hover over “Insert,” and select “Module” to add a new module.

Copy and paste the following VBA code into the module:

Sub CombineTextWithPrompt()
    Dim sourceRange As Range
    Dim destinationCell As Range
    
    ' Prompt user to select source range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the range of cells with text to combine", Type:=8)
    On Error GoTo 0

    ' Exit if the user cancels the selection
    If sourceRange Is Nothing Then
        Exit Sub
    End If
    
    ' Prompt user to select destination cell
    On Error Resume Next
    Set destinationCell = Application.InputBox("Select the destination cell to display the combined text", Type:=8)
    On Error GoTo 0

    ' Exit if the user cancels the selection
    If destinationCell Is Nothing Then
        Exit Sub
    End If
    
    ' Combine text from source cells and place it in the destination cell
    destinationCell.Value = Join(Application.Transpose(sourceRange.Value), "")
End Sub

Close the editor and return to your Excel workbook.

Press ‘Alt + F8‘ to open the “Macro” dialog box.

Select the macro named “CombineTextWithPrompt”

and click “Run.”

The VBA code will prompt you to select the range of cells with text to combine and the destination cell to display the combined text. Once executed, the selected destination cell will contain the concatenated text.

5. Video: Combine text

This Excel video tutorial where we’ll explore four methods to combine text from multiple cells into one. Join us as we delve into the first method using the Ampersand symbol, the second method employing the CONCATENATE function, the third method harnessing the TEXTJOIN function, and the fourth method utilizing VBA code.

6. Related Formulas

• Remove Numeric Characters from a Cell
  If you want to remove numeric characters from alphanumeric string, you can use the following complex array formula using a combination of the TEXTJOIN function, the MID function, the Row function, and the INDIRECT function..…
• remove non numeric characters from a cell
  If you want to remove non numeric characters from a text cell in excel, you can use the array formula:{=TEXTJOIN(“”,TRUE,IFERROR(MID(B1,ROW(INDIRECT(“1:”&LEN(B1))),1)+0,””))}…

7. Related Functions

• Excel Concat function
  The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…
• Excel CHAR function
  The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
• Excel TEXTJOIN function
  The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…