Excel Formulas to Combine Non-Adjacent Cells with a Slash

Manually merging non-adjacent Excel cells with a slash delimiter is a tedious, error-prone struggle for busy professionals. While standard formulas like basic concatenation serve as the traditional funding sources for database structure, they often fail when skipping empty fields. Utilizing the TEXTJOIN function grants users the power to effortlessly bypass blanks while linking disparate data points. However, this approach carries the stipulation that your system must run Excel 2019 or Office 365. For example, combining cells A1, C1, and E1 seamlessly outputs "US/2024/Active". Below, we outline the exact step-by-step formula syntax to streamline your workflow.

When working in Excel, you often need to merge data from different columns into a single, cohesive string. While combining adjacent cells (like a continuous row or column) is straightforward, combining non-adjacent cells-such as joining cell A2, C2, and E2 while skipping B2 and D2-requires a bit more strategy. Adding a specific; a slash (/) between these values is a common requirement for creating file paths, compound keys, dates, or product codes.

Depending on your version of Excel, there are several ways to accomplish this. In this guide, we will explore the best formulas and techniques to combine non-adjacent cells with a slash, ranging from modern dynamic functions to legacy workarounds and VBA solutions.

Method 1: The Modern Standard – The TEXTJOIN Function

If you are using Excel 2019, Excel 2021, or Microsoft 365, the absolute best tool for this job is the TEXTJOIN function. Un; older concatenation functions, TEXTJOIN allows you to specify a; and automatically apply it between all selected cells. Crucially, it also gives you the option to skip empty cells, preventing awkward double-slashes (like Value1//Value3).

Syntax of TEXTJOIN

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The character you want to put between cells. In our case, this is "/".
• ignore_empty: A boolean value. Use TRUE to skip empty cells, or FALSE to include them.
• text1, text2, ...: The individual non-adjacent cells or ranges you want to combine.

Step-by-Step Example

Imagine you have the following data structure in row 2:

• Cell A2: "USA"
• Cell C2: "California"
• Cell E2: "San Francisco"

To combine these three non-adjacent cells with a slash, enter the following formula in your destination cell:

=TEXTJOIN("/", TRUE, A2, C2, E2)

The Result: USA/California/San Francisco

Why TEXTJOIN is Superior

If Cell C2 (California) was empty, and you set the second argument to TRUE, the formula would elegantly output USA/San Francisco instead of USA//San Francisco. This built-in error prevention makes TEXTJOIN the cleanest approach available.

Method 2: The Classic Approach – The Ampersand (&) Operator

If you are using an older version of Excel (such as Excel 2016, 2013, or 2010), the TEXTJOIN function is not available. In these legacy versions, the most common and reliable method is using the Ampersand (&) operator to manually concatenate the cells and slashes.

The Basic Formula Syntax

=A2 & "/" & C2 & "/" & E2

This tells Excel to take the value of A2, append a slash, append the value of C2, append another slash, and finally append E2.

The Problem with Empty Cells (and How to Fix It)

The primary drawback of the basic Ampersand method is how it handles empty cells. If C2 is empty, the formula =A2 & "/" & C2 & "/" & E2 yields USA//San Francisco.

To prevent these double slashes, you must embed IF statements into your concatenation formula to check if each cell is blank before adding the slash. Here is how you can write a robust formula:

=A2 & IF(C2="", "", "/" & C2) & IF(E2="", "", "/" & E2)

How this works:

• It starts with the first cell, A2.
• For the second cell (C2), it checks if it's empty. If it is, it adds nothing (""). If it contains data, it adds a slash followed by the value of C2.
• It repeats this conditional check for E2 and any subsequent non-adjacent cells.

Method 3: The CONCATENATE Function

The CONCATENATE function is another legacy method. Functionally, it behaves identically to the Ampersand operator but uses a standard function wrapper.

Formula Syntax

=CONCATENATE(A2, "/", C2, "/", E2)

While this method works, it is generally less popular than the Ampersand operator because it requires more typing and offers no structural advantages. Microsoft has officially deprecated CONCATENATE in favor of the newer CONCAT function, though it remains available for backward compatibility.

Handling Dates and Numbers in Combined Cells

One common pitfall when combining cells in Excel is dealing with formatted data, such as dates or currency. If cell A2 contains "Project X" and cell C2 contains the date "2023-10-25", directly combining them using either TEXTJOIN or the Ampersand operator will yield a result like this:

Project X/45224

This happens because Excel stores dates as serial numbers (45224 represents October 25, 2023). To preserve the visual formatting of the date, you must wrap the cell reference inside the TEXT function.

Using the TEXT Function with Slashes

To combine "Project X" in A2 with the formatted date in C2, use this formula:

=A2 & "/" & TEXT(C2, "yyyy-mm-dd")

If you are using TEXTJOIN, the formula looks like this:

=TEXTJOIN("/", TRUE, A2, TEXT(C2, "yyyy-mm-dd"))

This ensures your dates look correct in the final combined output.

Method 4: Using VBA for Older Excel Versions

If you are stuck on an older version of Excel but need to combine dozens of non-adjacent cells without writing a massive, unmanageable IF/& formula, you can create a custom User-Defined Function (UDF) using VBA that mimics TEXTJOIN.

The VBA Code

To add this custom function to your workbook:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Function CombineNonAdjacent(Delimiter As String, IgnoreEmpty As Boolean, ParamArray Cells() As Variant) As String
    Dim CellVal As Variant
    Dim Result As String
    Dim i As Long
    
    Result = ""
    For i = LBound(Cells) To UBound(Cells)
        ' Check if the argument is a Range or a direct value
        If TypeOf Cells(i) Is Range Then
            For Each CellVal In Cells(i).Cells
                If Not (IgnoreEmpty And IsEmpty(CellVal.Value)) Then
                    Result = Result & CellVal.Value & Delimiter
                End If
            Next CellVal
        Else
            If Not (IgnoreEmpty And Cells(i) = "") Then
                Result = Result & Cells(i) & Delimiter
            End If
        End If
    Next i
    
    ' Remove trailing delimiter
    If Len(Result) > 0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    CombineNonAdjacent = Result
End Function

Close the VBA editor. You can now use this custom function in your worksheet just like a regular Excel formula:

=CombineNonAdjacent("/", TRUE, A2, C2, E2)

Summary Comparison of Methods

To help you choose the best route for your specific spreadsheet, here is a quick comparison of the primary methods:

Conclusion

Combining non-adjacent cells with a slash in Excel is a task with several solutions depending on your software version. If you are on a modern version of Excel, always default to TEXTJOIN for its efficiency and elegant handling of empty spaces. For older versions, the versatile Ampersand (&) operator paired with conditional IF logic remains the most reliable native workaround. By mastering these formulas, you can cleanly format and structuralize your spreadsheet data with ease.