In VBA, concatenate strings by joining them with the ampersand operator (&), such as fullName = firstName & " " & lastName. Use Join when you have an array of text items. Before running a macro, save a copy of your workbook: VBA changes cannot be undone with Ctrl+Z.
What You’ll Need
- Excel for Microsoft 365 on Windows. The examples also work in many earlier desktop versions of Excel that support VBA.
- A workbook saved as an Excel Macro-Enabled Workbook (
.xlsm) if you want to keep the macro in the file. - Access to the Visual Basic Editor. On Windows, press Alt+F11; on Mac, use Option+F11, or Fn+Option+F11 on some keyboards.
- A copy of the workbook saved before you run a macro. Excel for the web can open macro-enabled workbooks, but it cannot create or run VBA macros; use the desktop app.
Step-by-Step
- Decide what you want to join. A VBA string is a sequence of text characters. You can concatenate fixed text, string variables, values from worksheet cells, or items in an array. Decide whether the result needs spaces, commas, line breaks, or another separator. VBA does not add separators automatically, so include them as part of the expression.
- Save a backup and open the VBA editor. Save a separate copy of your workbook before running any macro. Then press Alt+F11 in Excel for Windows. In Excel for Mac, use Option+F11; if the function keys control hardware features, hold Fn as well. If Excel asks about macro security, only enable macros in files you trust.
- Add a standard module. In the Visual Basic Editor, choose Insert > Module. A blank code window appears. A standard module is a convenient place for a general-purpose macro; you do not need to add a worksheet button or enable the Developer tab to try this example.
- Write a basic concatenation macro. The ampersand operator joins the text on its left and right. In the example, the middle expression is one space inside quotation marks, so the names do not run together. Type or paste the complete procedure into the module.
Sub ShowFullName()
Dim firstName As String
Dim lastName As String
Dim fullName As String
' Set example text, then join it with a space.
firstName = "Avery"
lastName = "Chen"
fullName = firstName & " " & lastName
MsgBox fullName, vbInformation, "Full name"
End Sub
Running this procedure displays Avery Chen. The ampersands join three pieces: the first name, a space, and the last name. You can replace the example values with your own text or assign the variables from cells, forms, or other parts of your program.
- Run the macro and check the result. Click inside
ShowFullNamein the editor and press F5. You can also return to Excel, press Alt+F8 on Windows, selectShowFullName, and choose Run. On Mac, use the Macro command in Excel to select and run a procedure; shortcut availability can vary by keyboard setup. The message box should show the joined text. If you edit the macro, run it again to confirm the change. - Join more than two pieces and add punctuation. You can chain together as many expressions as needed. Put separators in quoted strings, and include punctuation explicitly. For example, to create a city-and-region label, use the following procedure. It displays
Portland, OR.
Sub ShowLocation()
Dim city As String
Dim region As String
Dim label As String
city = "Portland"
region = "OR"
label = city & ", " & region
MsgBox label, vbInformation, "Location"
End Sub
For a longer expression, you can use VBA’s line-continuation character: a space followed by an underscore at the end of a line. Keep the underscore outside the quoted text. Breaking a long statement across lines can make it easier to check that each separator is present and in the right place.
- Concatenate text stored in worksheet cells. Use a worksheet variable to specify which sheet to read from, and write the completed text to a destination cell. This example reads nonblank entries in A2:A20 on a sheet named
Sheet1and puts them in B2, separated by commas. Change the sheet name and range to match your workbook.
Sub JoinNamesFromColumn()
Dim ws As Worksheet
Dim rowNumber As Long
Dim cellText As String
Dim combined As String
' Use the worksheet that contains the source list.
Set ws = ThisWorkbook.Worksheets("Sheet1")
For rowNumber = 2 To 20
cellText = Trim$(CStr(ws.Cells(rowNumber, "A").Value2))
If Len(cellText) > 0 Then
If Len(combined) > 0 Then
combined = combined & ", "
End If
combined = combined & cellText
End If
Next rowNumber
' Write the joined list to the destination cell.
ws.Range("B2").Value = combined
End Sub
For example, if A2 contains Red, A3 is blank, A4 contains Green, and A5 contains Blue, B2 receives Red, Green, Blue. The blank-cell check prevents extra commas for empty entries. This code uses Trim$ to remove spaces at the beginning and end of each entry; remove that call if those spaces are meaningful in your data. Make sure the named worksheet exists or VBA will stop with a subscript error.
- Use Join for an array of text items. If the pieces are already in an array,
Joincombines them using the separator you provide as its second argument. The following macro displaysVBA joins text. The array is a Variant because theArrayfunction returns an array that can be stored in a Variant.
Sub JoinWords()
Dim words As Variant
Dim sentence As String
words = Array("VBA", "joins", "text")
sentence = Join(words, " ")
MsgBox sentence, vbInformation, "Joined words"
End Sub
Use Join when your items are collected together in an array and share one separator. For a few individual variables, the ampersand operator is usually easier to read. With either method, save the workbook and test the output against a small, known example before adapting the code to a larger data set.
Common Mistakes
- Using plus instead of ampersand. VBA’s
+operator can add numbers, and it may also join text in some circumstances. That dual purpose can cause unexpected results or type mismatch errors. Use&when your intention is to concatenate text. - Leaving out the separator.
firstName & lastNameproduces text such asAveryChen. Add& " " &for a space, or include a comma, slash, or other delimiter in a quoted string. - Forgetting quotation marks around literal text. In an expression,
"-"means a literal hyphen, whileseparatormeans a variable named separator. Use straight quotation marks in VBA code and check that each opening quote has a matching closing quote. - Assuming displayed cell formatting will be copied.
Value2reads a cell’s underlying value, not its display format. A date may therefore be joined as a number rather than in the date style shown on the worksheet. UseFormat$to create a text representation when you need a particular date or number format. - Ignoring blank cells or error values. Empty cells may create repeated separators unless you skip them, as in the loop above. A cell containing an Excel error may also fail when converted with
CStr; check for errors before converting data that may contain them.
Tips
- Use
&consistently for string concatenation. It makes your intent clear to anyone maintaining the code and avoids confusing text joining with arithmetic. - Build a long result in a clearly named variable, such as
fullAddressorcombined, rather than nesting a complicated expression inside a message box or cell assignment. - Choose separators deliberately. A space works for names, a comma and space for a list, and
vbCrLffor a new line in a message box or other text output that supports line breaks. - For predictable date text, use
Format$with an explicit format, such asFormat$(dateValue, "yyyy-mm-dd"). This avoids relying on a computer’s regional date display. Choose the format that suits the intended reader. - When joining many worksheet values, read from the correct sheet and keep the output destination separate from the source range. If you need to process thousands of cells, consider reading the range into an array first rather than repeatedly accessing worksheet cells inside a loop.
- Use descriptive variable names and short comments to explain any non-obvious separator or formatting choice. These small details make it easier to update the macro later without changing the result by accident.
Troubleshooting
The editor reports a compile error. Check the line VBA highlights. Look for a missing ampersand, an unmatched quotation mark or parenthesis, a misspelled variable, or a line-continuation underscore that is not preceded by a space. Also confirm that each procedure has a matching End Sub.
The macro does not appear in the Macro list. Confirm that the code is in a standard module and that the procedure is declared as a public, parameter-free Sub, not as a function or a worksheet event procedure. If the file is an .xlsx, save it as .xlsm to retain VBA code.
The macro stops when it refers to a worksheet. Check the spelling of Sheet1 against the sheet tab name, including spaces. If your code uses ThisWorkbook, it refers to the workbook containing the macro; use the intended workbook and worksheet explicitly rather than relying on whichever sheet happens to be active.
The result has missing spaces, extra commas, or unexpected numbers. Review the expression from left to right and add the intended separators between each item. For worksheet values, check whether cells are blank, contain errors, or store dates and numbers with display formatting that differs from their underlying values. Use Format$ where a specific text format is required.
Nothing runs when you press the shortcut. Make sure the cursor is inside the procedure before pressing F5 in the editor, or select the macro by name from Excel’s Macro dialog. Confirm that macros are permitted for this trusted workbook. Excel for the web cannot run VBA, so open the file in the desktop version instead.
Frequently Asked Questions
What is the VBA operator for concatenating strings?
Use the ampersand operator, &. For example, result = "Hello, " & userName combines the greeting and the value of userName.
Can I concatenate strings with the plus sign in VBA?
VBA may use + to join text, but it also performs numeric addition, which can make the result ambiguous. Prefer & whenever you mean to combine strings.
How do I add a space between concatenated values?
Include a space as a quoted string between the values: firstName & " " & lastName. For a comma and space, use ", " as the separator.
How do I concatenate a range of cells?
Loop through the cells, append each nonblank value to a string variable, and add a separator only between included values. The example above reads A2:A20 and writes the combined text to B2.
When should I use Join instead of ampersand?
Use Join when you have an array of strings and want to put the same delimiter between each item. Use & for a few individual values or when the separators differ throughout the expression.
Can I undo a VBA concatenation macro with Ctrl+Z?
No. Excel generally clears its undo history when a macro runs, so Ctrl+Z cannot reverse the macro’s changes. Save a separate workbook copy before running code that writes or changes worksheet data.
Related
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Function As Boolean: How to Return True or FalseYes. In VBA, declare a function with As Boolean when it should return either True or False, then…
- VBA ArrayList: How to Create and Use It in Excel VBAIn VBA, an ArrayList is a resizable, zero-based list that you can create on Windows with CreateObject("System.Collections.ArrayList"). It…
- Excel VBA FindNext: How to Find the Next MatchIn Excel VBA, use Range.FindNext to move from one match to the next after starting a search with…
- Google Sheets Macros: How They Work and What to KnowYes. Google Sheets supports macros: you can record repeated actions and run them again, or edit the generated…
- VBA Code to Save a Workbook in Excel: Practical ExamplesTo save the workbook that contains your VBA code, use ThisWorkbook.Save; to save the active workbook, use ActiveWorkbook.Save.…
- VBA Delete Row If Cell Is Blank: Safe MethodsTo delete rows when a cell in a chosen column is blank, loop through the data from the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.