How to reverse the text to column function in Excel?
Excel's Text to Columns feature enables users to split the data in a single column into multiple columns based on specified delimiters, such as spaces, commas, or custom characters. While this tool is invaluable when dealing with data imported from other sources or files where information is grouped together, you might later realize you need to combine those separated values back into a single cell—essentially reversing the split. This situation often arises when you want to recreate full names from first and last names, join address components, or prepare concatenated data for exports or reports.
In this article, you will discover several practical methods to reverse the Text to Columns operation and recombine multiple columns into one cell. We will present step-by-step techniques using Excel formulas, an efficient Kutools for Excel feature, and a custom VBA function. These solutions are suitable for various scenarios—from small data ranges to large-scale merging—and we'll also discuss when to use each method, considerations, and troubleshooting tips to ensure smooth results.
Reverse the text to columns function with formulas
In many scenarios, you can use simple Excel formulas to recombine separated columns into one. This is especially useful when dealing with a moderate number of columns or when you require a customizable delimiter (such as a space, comma, or hyphen). This method is widely applicable and doesn't require any add-ins or code—just an understanding of basic formula syntax.
1. Select the cell where you want to display the combined value. For demonstration, we assume you want to join the contents of cells A2, B2, and C2.
=A2&" "&B2&" "&C2=CONCATENATE(A2," ",B2," ",C2)These formulas combine the contents of cells A2, B2, and C2 with a space as the separator. If you wish to use a different separator (such as a comma, semicolon, or hyphen), simply replace the space (" ") in the formula with your preferred character, for example: =A2&","&B2&","&C2.
2. Once you have entered the formula for the first row, use the fill handle to drag the formula down to other rows where you want the same merging. To do this quickly, move your cursor to the lower-right corner of the selected cell (it will turn into a plus sign), then click and drag to fill the formula for adjacent rows.
Note: In these formulas, A2, B2, and C2 represent the cells you want to join. Adjust these references if your data is in different columns. Always ensure your formula references match the location of your data. The delimiter in the formula can be changed to any character, depending on your reporting or formatting needs.
Tip: The =A2&" "&B2&" "&C2 approach is quick for a limited number of columns. For data with many columns to combine, see the advanced solutions below for greater efficiency.
Cautions: When using CONCATENATE or & symbols, be careful not to omit any delimiters unless you want values combined without separation. Double-check your formula for extra spaces that could affect data consistency. In older versions of Excel (before Excel2016), =CONCATENATE() is available, while newer versions also support =CONCAT() and =TEXTJOIN() for enhanced functionality.
Reverse the text to columns function with Kutools for Excel
If you have Kutools for Excel installed, you can efficiently merge multiple rows, columns, or cell ranges into a single cell or column without writing formulas. The Combine Rows, Columns or Cells without Losing Data feature is ideal for users who want a fast, reliable, and flexible approach, especially when working with large datasets, varying delimiters, or advanced merging options such as combining while keeping/deleting the original content or merging into a single cell. It's also useful if you are not comfortable with formulas or VBA.
See the demonstration below to understand how Kutools for Excel simplifies the reversal of the Text to Columns action:
Once Kutools for Excel is installed and activated, follow these steps to reverse the split:
1. Select the range of cells you want to merge (these are the columns previously separated by Text to Columns).
2. Go to the Kutools tab, then click Merge & Split > Combine Rows, Columns or Cells without Losing Data. This opens the dedicated combining window.
3. In the dialog box, configure the following options for best results:
(1) Choose Combine columns to merge the selected columns for each row.
(2) Select your preferred separator under Specify a separator (e.g., space, comma, semicolon, or a custom value).
(3) Decide how to handle the original cells—keep contents, delete contents from the merged area, or merge the cells into one across columns.
4. Click OK or Apply. Instantly, the separate columns are recombined according to your configuration.
Advanced users can also explore additional options within Kutools to batch process, filter before combining, or integrate merging with other Kutools utilities.
Download and free trial Kutools for Excel Now!
Reverse the text to columns function with User Defined Function
If you need to combine a large number of columns and basic formulas become unwieldy (for example, when joining more than ten columns), a User Defined Function (UDF) created with VBA can make this task simple and scalable. With a custom VBA function, you can merge any number of columns or ranges with one concise formula, specifying your separator as needed.
This approach is especially helpful for recurring tasks or when automated merging is required across different ranges or workbooks.
1. Press ALT + F11 to open the Microsoft Visual Basic for Applications editor window.
2. In the VBA editor window, select Insert > Module, then paste the provided User Defined Function code into the module window.
VBA code: Reverse the text to columns function:
Public Function ReverseTextToColumns(Rg As Range, Optional D As String = " ") As String
'updateby Extendoffice
Dim xArr
xArr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(Rg.Value))
ReverseTextToColumns = Join(xArr, D)
End Function
3. Save and close the VBA editor. Back in your worksheet, in a target cell where you want the combined value, enter the following formula (replace the range and delimiter as needed):
=reversetexttocolumns(A2:C2,",")Then, use the fill handle to drag the formula down to other rows where you want to apply the combination. All the values from the specified columns will be joined into a single cell with your chosen delimiter.
Note: In this example, A2:C2 is the range of columns to be combined, and “,” is the separator. Modify these parameters according to your data. The function will process arrays of cells horizontally; for vertical data, adjust the range accordingly.
Pro tip: Custom UDFs are powerful for automating repetitive merges or applying complex joining rules. However, remember to save your workbook as a macro-enabled file format (.xlsm) after using VBA.
Troubleshooting: Enable macros to ensure the UDF functions correctly. If you receive a #NAME? error, check that the VBA code was inserted into a module and is saved and available in the workbook.
Reverse the text to columns function with TEXTJOIN Formula (Excel2019+)
In Excel2019 and later versions, you can use the TEXTJOIN function for an even more streamlined approach to combine multiple columns or ranges into a single cell. TEXTJOIN supports delimiters and can skip empty cells, making it both powerful and flexible for reversing Text to Columns, especially when dealing with irregular datasets.
1. In your target cell (e.g., D2), enter the following formula to join columns A2, B2, C2 with a space separator:
=TEXTJOIN(" ",TRUE,A2:C2)2. Press Enter to confirm. If you need to apply the formula to multiple rows, drag the fill handle down. You can adjust the delimiter " " (space) to any character as needed. The TRUE parameter tells Excel to ignore empty cells in the range.
Note: TEXTJOIN requires Excel2019 or a later version. This formula is extremely versatile for datasets with many columns, and helps keep your formulas concise.
In summary, there are several ways to reverse the Text to Columns function in Excel, allowing you to recombine separated data efficiently and flexibly. For small and straightforward cases, basic formulas are sufficient. For complex or large datasets, Kutools for Excel offers a fast, configuration-based approach, while UDF provide advanced customization and automation capabilities. Finally, newer Excel functions such as TEXTJOIN make merging columns easier than ever. Always choose the method that aligns with your Excel version, data size, and personal preferences.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in