How to append or adding text from one cell to another cell in Excel?
In everyday spreadsheet work, it’s common to find yourself needing to combine values from multiple cells into a single cell. For example, you might have first, middle, and last names in separate columns that you want to bring together, or you need to merge fragmented address or data values for better readability or processing. As illustrated in the screenshot below, the task is to append or add text from different cells into one cell in Excel, achieving a consolidated result that suits your data management needs.
How can this be achieved efficiently and accurately? In this article, multiple methods are presented in detail, covering both formula-based solutions and robust utilities, to help you quickly append or combine text from one cell to another in Excel—no matter the size or complexity of your data.
Appending text from one cell to another with formula
Easily appending text from one cell to another with Kutools for Excel
Appending text using the Ampersand (&) operator
Appending text using the TEXTJOIN function (Excel2016 and later)
Appending text from multiple cells using VBA code (macro)
Appending text from one cell to another with formula
One effective way to combine text from several cells in Excel is by using a formula. This method is suitable for users who prefer in-cell solutions and do not wish to use add-ins or additional tools.
1. Select a blank cell where you want the combined result to appear. Enter the following formula into the formula bar: =CONCATENATE(A1," ",B1," ",C1) and press Enter to apply.
2. Next, select cell D1 (or your formula cell) and drag the Fill Handle down or across to extend the formula to adjacent rows or columns as needed. This allows you to quickly combine cell values for an entire dataset.
Note: In this formula, you can adjust the referenced cells (A1, B1, C1) and separators (such as space, comma, or hyphen) according to your specific requirements. The CONCATENATE function is available across Excel versions, making it broadly applicable, though in newer Excel versions, alternatives like TEXTJOIN or CONCAT are also available with additional flexibility.
Easily appending text from one cell to another with the Combine utility of Kutools for Excel
The Combine Rows, Columns or Cells without Losing Data utility included in Kutools for Excel provides a fast and streamlined way to merge or append text across a range of cells. This method is especially valuable for users who want to avoid manual formulas or need to combine large datasets with custom separators, placement, and post-processing options. The utility not only simplifies the merging process, but also offers great flexibility in terms of result formatting and cell management.
1. If you want to append values from adjacent cells (for instance, several columns of names or data), first select the cell range. Then navigate to Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data. Refer to the screenshot for visual guidance:
2. In the Combine Rows, Columns or Rows Without Losing Data dialog box, adjust the settings as needed:
- A. Select Combine columns if the cells to append are in different columns.
- B. Set your desired separator in the Specify a separator field (for example, Space, Comma, Semicolon, or Custom).
- C. Choose where the combined results should be placed using the Place the results to dropdown list.
- D. If you want to remove the original individual values after combining, select Delete contents of combined cells.
- E. Click OK to confirm and run the operation.
The cell values from different columns will now be combined according to your settings, and the result will appear in the location you specified.
Note:
If you select the Combine rows option instead, your data from each row is merged as shown in the screenshot below, which is useful when you want to concatenate multiple rows within one column.
Advantages and practical tips: This utility is optimal for processing large tables or when you require advanced options (custom separators, retaining/deleting source data, flexible placement). It minimizes manual errors, saves time, and is especially recommended for users who frequently work with data consolidation tasks.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Appending text using the Ampersand (&) operator
The ampersand (&) operator in Excel is a straightforward and universally available method to combine texts from multiple cells. It is particularly useful for quick concatenation tasks when you do not require advanced options, and it works in all Excel versions. This method is convenient for basic appending, such as merging names, addresses, or any short data in adjacent cells.
1. Select the target cell where you want to append text (for example, D1), and enter the following formula:
=A1 & " " & B1 & " " & C1
2. Press Enter to confirm, and use the Fill Handle to copy the formula to other rows as needed.
Explanation & tips: This method is simple and quick for a few cells. You may use any delimiter (such as “-” or “, ”) by replacing spaces inside quotation marks. For longer ranges, this method requires manual editing to include each cell reference. Always be cautious to use quotation marks around your chosen separator.
Appending text using the TEXTJOIN function (Excel 2019 and later)
For users of Excel 2019 or newer, the TEXTJOIN function is a flexible and efficient way to append text from an entire range of cells using a specified delimiter. This function is highly recommended for merging many cells, handling ranges dynamically, and automatically ignoring blank cells if desired.
1. In the cell where you want the combined result to appear (for example, D1), enter the following formula, using space as a separator:
=TEXTJOIN(" ",TRUE,A1:C1)
2. Press Enter to execute the formula. If you wish to combine more rows, simply copy or drag the formula down alongside your dataset.
Parameter explanations: The first argument " "
sets the separator (space in this example). The second argument TRUE
means any blank cells will be ignored. Change A1:C1
to fit your actual data range.
Advantages and considerations: This method is ideal for large ranges and dynamic data, especially in modern Excel environments. For earlier Excel versions, consider the CONCATENATE function or ampersand operator instead. Note that typing the range (e.g., A1:C1
) allows you to concatenate entire rows or columns at once, eliminating manual reference.
Error reminders: If TEXTJOIN
is not available in your version, you may see a #NAME?
error.
Appending text from multiple cells using VBA code (macro)
When you need to concatenate text from multiple or even nonadjacent cells—possibly spanning multiple worksheets or complex ranges—a VBA macro provides a programmable and highly automated alternative. This solution is ideal for repetitive tasks, large datasets, or custom logic beyond built-in formulas and functions.
Applicable scenarios: Suitable for users comfortable with macros or those managing large volumes of data consolidation, especially when automation or repetitive operations are required.
1. Open the VBA editor via Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, choose Insert > Module, and paste the following code into the Module:
Sub MergeCellsRowByRow()
'Updated by Extendoffice 2025/7/15
Dim WorkRng As Range
Dim Delimiter As String
Dim OutputCell As Range
Dim rowRng As Range
Dim cell As Range
Dim Combined As String
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Select range to merge
Set WorkRng = Application.InputBox("Select range to merge by row:", xTitleId, Selection.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
' Enter delimiter
Delimiter = Application.InputBox("Enter a separator:", xTitleId, " ", Type:=2)
' Select output starting cell
Set OutputCell = Application.InputBox("Select starting output cell:", xTitleId, "", Type:=8)
If OutputCell Is Nothing Then Exit Sub
On Error GoTo 0
Application.ScreenUpdating = False
' Process each row
For i = 1 To WorkRng.Rows.Count
Combined = ""
For Each cell In WorkRng.Rows(i).Cells
If cell.Value <> "" Then
Combined = Combined & cell.Value & Delimiter
End If
Next
' Remove trailing delimiter
If Len(Combined) > 0 Then
Combined = Left(Combined, Len(Combined) - Len(Delimiter))
End If
OutputCell.Offset(i - 1, 0).Value = Combined
Next i
Application.ScreenUpdating = True
End Sub
2. Click the button to run the macro. Follow the prompts: first, select the range to merge, then enter your separator (such as a space, comma, or other character), and finally select the output cell for the result.
Practical tips:
- Macros can significantly speed up the task if you have a large amount of data or need to merge text frequently.
- The code ignores blank cells automatically, so you don’t need to pre-filter your data.
- Make sure to save your work before running macros, and enable macros if prompted by Excel security warnings.
Summary suggestions:
- When combining a small or fixed number of cells, formulas or the ampersand operator provide quick and effective results.
- For dynamic ranges or large datasets, leverage the TEXTJOIN function or advanced features of Kutools for Excel.
- When regular manual combining is impractical, or you need to automate across multiple worksheets, VBA macros offer unmatched flexibility and customization.
- Always double-check cell references and delimiters to ensure accuracy in your combined outputs, and keep a backup of data before applying irreversible actions.
Related article:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!