VBA & Macro Problems

How to Add Spaces with VBA CONCATENATE and TEXTJOIN in Excel

Tauseeq Magsi Oct 9, 2026 869 views

Question details

The user needs to know how to explicitly include a space separator when combining cell values using VBA formulas like CONCATENATE and TEXTJOIN.

How to Add Spaces with VBA CONCATENATE and TEXTJOIN

Product: Spreadsheets
Device & OS: not provided
Scenario: Writing VBA code to concatenate strings from multiple cells and inserting a space character between them.
Observed behavior: Without proper formatting, the VBA editor either throws a syntax error due to unmatched quotation marks or combines the text directly without a space. Additionally, evaluated formulas might not output to the destination cell automatically.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to preserve your VBA code.

Solution 1Recommended

Use Doubled Quotation Marks for CONCATENATE and Ampersand Formulas

To insert a space within a VBA string formula, you must double the quotation marks representing the space so the VBA editor correctly parses the string.

When assigning formulas directly to a cell via VBA using the FormulaR1C1 or Formula properties, the entire formula is wrapped in quotation marks. To include a literal quotation mark inside this string (such as the ones wrapping a space character), you must double them.

Select Target Cell

Determine the destination cell where you want the concatenated result to appear.

Apply CONCATENATE Formula

To use the CONCATENATE function, enter the following code in your VBA module: ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[3],"" "",RC[4])"

Alternative: Use Ampersand Operator

For a shorter syntax using the ampersand (&) operator, use this code: ActiveCell.FormulaR1C1 = "=RC[3]&"" ""&RC[4]"

Use Doubled Quotation Marks for CONCATENATE and Ampersand Formulas

Syntax Tip: Using the ampersand (&) operator is often preferred by developers for its readability and concise syntax compared to the CONCATENATE function.

Solution 2

Use TEXTJOIN with the Evaluate Method

When using TEXTJOIN via the ActiveSheet.Evaluate method, ensure you capture the result in a variable and assign it to the destination cell.

Advanced VBA Support in WPS

Run VBA Macros Seamlessly with WPS Office

WPS Office Spreadsheets provides robust, built-in support for VBA macros. You can easily write, edit, and run your CONCATENATE and TEXTJOIN scripts to automate data entry without switching to heavier applications.

1. Download and Install: Download WPS Office for free from the official website and install it on your device.
2. Enable Developer Mode: Open WPS Spreadsheets, go to the Options menu, and enable the Developer tab to access macro tools.
3. Open the VBA Editor: Click 'Visual Basic' in the Developer tab to open the editor and paste your text joining macros.
4. Run Your Code: Press F5 or click the Run button to execute your VBA code and perfectly combine your cell data with spaces.

Fully compatible with Microsoft Excel VBA scripts and macrosIntegrated Visual Basic editor for writing and debugging codeHighly compatible with Microsoft Excel (.xlsm) formatsLightweight application that opens quickly and runs smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA editor highlight the line in red when I add a space?

This happens when you use single quotation marks for the space inside a VBA string. VBA interprets the single quotation mark as the end of the text string, causing a syntax error. You must double the quotation marks (e.g., "" "") to escape them correctly.

Can I use the ampersand (&) operator instead of CONCATENATE in VBA?

Yes, the ampersand operator is fully supported, faster, and often easier to read. You can construct the formula inside your VBA code like this: ActiveCell.FormulaR1C1 = "=RC[3]&"" ""&RC[4]".

Why is my Evaluate TEXTJOIN result not showing in the target cell?

The Evaluate method calculates the formula result in the background memory but does not automatically write it to the active worksheet. You must explicitly assign the calculated variable to a cell's value, for example using Range("E2").Value = v.

Excel & Data Problems