Concat in Excel VBA: Join Text with the & Operator

VBA & macros · Updated November 18, 2026

To concatenate text in Excel VBA, use the ampersand operator, &, to join values, such as fullName = firstName & " " & lastName. For a one-dimensional array of strings, VBA also provides Join; for cells, loop through the range and build the result.

What “concat” means in VBA

“Concat” is shorthand for concatenate: combine two or more pieces of text into one string. In VBA, the usual tool is the & operator. It joins values in the order they appear and returns a combined string. You can concatenate variables, literal text in quotation marks, cell values, or a mixture of these.

For example, joining a first name, a space, and a surname produces one full name. Joining a product code and a suffix can create an identifier. The operator itself does not add spaces, commas, line breaks, or other separators: include those explicitly as strings when you need them.

VBA is Excel’s macro programming language, so this is different from concatenating with a worksheet formula in a cell. Excel’s worksheet functions include CONCAT and TEXTJOIN, while VBA code uses operators and language features such as Join. Choose the method that fits where the work should happen: a formula recalculates in the sheet, whereas a macro runs instructions and can write results into cells.

Use the ampersand operator

The ampersand is generally the clearest choice for joining a small number of values. Its syntax is simply an expression, an ampersand, and another expression. Add more ampersands to include additional pieces. A string literal must be enclosed in straight double quotation marks, and a space between words must be supplied as " ".

Prefer & over the plus sign for text concatenation. VBA’s plus sign can perform numeric addition, so its meaning may depend on the values and types involved. The ampersand signals that the operation is text joining, making code easier to read and less likely to produce an unintended arithmetic result.

When a value is not already a string, VBA may convert it as part of a concatenation. For predictable formatting, convert or format values deliberately. For example, a date’s string representation can vary with regional settings; use VBA’s Format$ function if you need a particular display pattern.

Combine values from worksheet cells

This example reads a first name and surname from cells A2 and B2 on a worksheet named Sheet1, inserts a space, and writes the result to C2. It assumes the cells contain ordinary text or values that can be converted to text, not Excel error values.

Sub CombineTwoCells()
    Dim firstName As String
    Dim lastName As String
    Dim fullName As String

    ' Read the two source cells as text.
    firstName = CStr(Worksheets("Sheet1").Range("A2").Value)
    lastName = CStr(Worksheets("Sheet1").Range("B2").Value)

    ' Add a space between the names and write the result.
    fullName = firstName & " " & lastName
    Worksheets("Sheet1").Range("C2").Value = fullName
End Sub

CStr explicitly converts each cell value to a string. If either source cell is blank, this version still includes the separator, so a blank first name could produce a leading space. If that matters, test for blank values or trim the result. Also note that .Value returns the underlying value, not necessarily the exact text displayed in the cell. A date or number may therefore look different after conversion; apply Format$ when the output needs a specified date or number format.

Concatenate a range with a loop

For a range with several cells, a loop gives you control over ordering, blank cells, and separators. The following procedure reads A2:A5, skips empty cells, joins the remaining values with comma-space separators, and puts the result in B2. The range is processed from top to bottom.

Sub JoinRangeValues()
    Dim sourceCell As Range
    Dim outputText As String
    Dim separator As String

    separator = ", "

    ' Visit each cell and omit empty values.
    For Each sourceCell In Worksheets("Sheet1").Range("A2:A5").Cells
        If Len(CStr(sourceCell.Value)) > 0 Then
            If Len(outputText) > 0 Then
                outputText = outputText & separator
            End If
            outputText = outputText & CStr(sourceCell.Value)
        End If
    Next sourceCell

    ' Store the combined text in the destination cell.
    Worksheets("Sheet1").Range("B2").Value = outputText
End Sub

With “red” in A2, a blank A3, “green” in A4, and “blue” in A5, B2 becomes red, green, blue. Because the code skips empty cells, it does not create doubled commas for gaps. It assumes the cells do not contain Excel errors such as #N/A; converting an error value with CStr can fail. If errors are possible, check IsError(sourceCell.Value) before converting and decide whether to skip the cell or handle it separately.

Choose a separator and handle blanks

A separator is simply another string inserted between values. Common choices include a space, comma and space, hyphen, slash, or a line break. In VBA, vbCrLf represents a Windows-style line break. When writing a line break into a worksheet cell, the cell may need Wrap Text enabled to display multiple lines clearly.

Decide how blanks should behave before building a range concatenation. Skipping blank cells avoids extra separators, as in the loop above. Keeping their positions can be important when each position represents a field, such as a fixed set of address components. In that case, append a separator for every position, even when the corresponding value is empty. These approaches produce different results, so choose based on the structure of the data rather than assuming one is universally correct.

For names, do not automatically add a space if both inputs may be blank or one may already include surrounding spaces. You can test the inputs, or use VBA’s Trim$ function to remove leading and trailing spaces from a finished string. Trimming does not remove spaces between words.

Use Join for a string array

The VBA Join function combines the elements of a one-dimensional string array, inserting an optional delimiter between them. It is convenient when the strings are already collected in an array. For example, the following code prints a comma-separated list in the Immediate window:

Sub JoinStringArray()
    Dim pieces(0 To 2) As String
    Dim combinedText As String

    ' Fill a one-dimensional string array.
    pieces(0) = "red"
    pieces(1) = "green"
    pieces(2) = "blue"

    ' Join the elements with comma-space separators.
    combinedText = Join(pieces, ", ")
    Debug.Print combinedText
End Sub

The output is red, green, blue. Join is not a direct replacement for the range loop: a range’s .Value commonly gives a two-dimensional Variant array, while Join expects a one-dimensional array of strings. For a worksheet range, use a loop to build a suitable array or use the loop to assemble the output directly. The latter is often simpler when you also need to skip blanks or validate values.

Put the macro in Excel and run it safely

In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and change the sheet name and ranges to match your workbook. Return to Excel and run a selected macro using Alt+F8, choose its name, and select Run. The Visual Basic Editor can also run a procedure while the cursor is inside it.

Excel for Mac also supports VBA in the desktop app. The editor is available from Tools > Macro > Visual Basic Editor; Option+F11 is a common editor shortcut, though some keyboards require Fn as well. Excel for the web does not run VBA macros, so use the desktop app to edit or execute this code. Menu labels and keyboard behavior can vary slightly with platform and keyboard settings.

Save a copy of the workbook before running a macro. Macro actions cannot be undone with Ctrl+Z, and code that writes to cells can overwrite existing data. If the workbook must retain VBA, save it in a macro-enabled format such as .xlsm. Only enable macros in files from sources you trust; do not lower macro security globally just to run an unfamiliar file.

Common errors and practical fixes

  • Missing spaces or punctuation: VBA adds only the text you specify. Insert an explicit string such as " " or ", " between values.
  • Using plus instead of ampersand: Replace text-joining uses of + with & to avoid confusion with numeric addition.
  • Unexpected date or number display: Convert values with an explicit format when the output must follow a particular pattern. A cell’s displayed formatting is not automatically guaranteed to become the same text in the result.
  • Type mismatch on an error cell: Check for an error value before converting a cell with CStr. Decide whether to skip it, write a placeholder, or stop with a useful message.
  • Extra separators: Check for blank values before adding a delimiter, or track whether a value has already been appended. This prevents leading, trailing, or doubled punctuation.
  • Nothing happens in Excel for the web: VBA requires desktop Excel. Opening a workbook in a browser does not execute its macros.

When debugging, use Debug.Print to inspect a string in the Immediate window before writing it to the sheet. This helps distinguish a problem in the concatenation from a problem with the destination cell, sheet name, or workbook state.

VBA concatenation versus worksheet alternatives

Use VBA when the concatenation is part of a repeatable process: for example, a macro that cleans imported data, constructs labels, or writes a batch of outputs. VBA can apply rules and update many cells in one run, but it requires a macro-enabled desktop workflow and does not automatically recalculate like a worksheet formula.

Use a worksheet formula when the result should update as source cells change and the logic belongs visibly in the grid. Excel’s CONCAT combines text values, while TEXTJOIN can combine a range with a delimiter and an option to ignore empty cells. Those are worksheet functions, not VBA language functions. Conversely, Join is a VBA function for arrays and is not entered as an Excel cell formula. Keep the distinction clear when searching for “concat Excel VBA”: the best method depends on whether the desired output is a live sheet result or a macro-generated value.

Frequently Asked Questions

What is the VBA equivalent of CONCAT?

For a few values, use the VBA ampersand operator, &. For an array of strings, use Join. Excel’s worksheet CONCAT function is a separate feature and is not required to concatenate text in VBA.

Can I concatenate a whole Excel range with Join?

Not directly in the usual case. A range’s .Value is generally a two-dimensional Variant array, while Join takes a one-dimensional string array. Loop through the cells, or convert the values into a one-dimensional string array first.

How do I add a space between values?

Include a space as a string in the expression, such as firstName & " " & lastName. For a range, set the loop’s separator to " " and insert it between values.

How do I skip blank cells when concatenating?

Test each cell before appending it, and add the separator only when a nonblank value is being added after an earlier value. The range-loop example above uses this pattern, so empty cells do not create extra commas.

Can VBA macros run in Excel for the web?

No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; use worksheet formulas or another supported automation option if the work must run in a browser.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.