How to concatenate texts of multiple cells into a textbox in Excel?
It may be easy for you to concatenate texts of multiple cells into a new cell with formula. But do you know how to concatenate multiple cell values into a textbox (ActiveX Control)? Actually, the method in this article can help you to achieve it.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
The below method can help you to concatenate texts of multiple cells into a textbox in Excel.
1. In the worksheet you want to concatenate multiple cells, insert a textbox by clicking Developer > Insert > Text Box (ActiveX Control). See screenshot:
2. Then click Developer > Insert > Command Button (ActiveX Control) to insert a command button into the worksheet.
3. Right-click the inserted command button, then select View Code from the context menu. See screenshot:
4. In the Microsoft Visual Basic for Applications window, please replace the original code with below VBA code.
VBA code: Concatenate texts of multiple cells into a textbox
Private Sub CommandButton1_Click() Dim xRg As Range Dim xCell As Range Dim xStr As String On Error Resume Next Set xRg = Application.Selection If xRg Is Nothing Then Exit Sub With Me.TextBox1 .Text = vbNullString .MultiLine = True .WordWrap = True For Each xCell In xRg xStr = xCell.Value .Text = Me.TextBox1 & xStr & Chr(10) Next End With End Sub
Note: CommandButton1 is the name of the inserted command button. Please change it based on your need.
5. Turn off the Design Mode by clicking Developer > Design Mode.
6. Select the cells you need to concatenate the texts into a textbox, then click the Command Button. And then all selected cells’ contents are concatenated into the textbox immediately as below screenshot shown.
- How to auto select text of a textbox when it is selected in Excel?
- How to autocomplete a textbox when typing in Excel?
- How to clear contents of textbox when clicked in Excel?
- How to disable editing in textbox to prevent user inputting in Excel?
- How to format a textbox as a percentage in Excel?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agohola Como el boton puede tomarme las celdas visibles, ya que a veces oculto celdas y me las toma igual pero no quiero
- To post as a guest, your comment is unpublished.· 1 years agoMuy bueno, Y si le quiero poner ; para separar los textos ¿cómo lo hago?