为何Google表格与Excel的SUBSTITUTE函数仅替换奇数位匹配项？

Hey, let's break down why this is happening and the best ways to fix it!

Why Only Odd Positions Are Being Replaced #

Excel's SUBSTITUTE function works with non-overlapping matches. When you have consecutive instances of the text you're replacing (like , '', repeated back-to-back), the first replacement messes up the structure of the adjacent match.

For example, if your string has , '', '', '',, the first SUBSTITUTE turns the first , '', into , NULL,, making the string , NULL, '',. Now the former second , '', is split into , NULL, and , '',—so it no longer matches your target pattern. The function skips it and finds the next full , '', (the original third instance), resulting in only odd positions getting replaced.

The Best Fixes #

Depending on your Excel version, here are the cleanest solutions:

1. Double SUBSTITUTE (Works for All Excel Versions)

Your temporary workaround is actually the most straightforward compatible fix. Nesting two SUBSTITUTE calls covers both odd and even positions:

=SUBSTITUTE(SUBSTITUTE(A1, ", '',", ", NULL,"), ", '',", ", NULL,")

The first call replaces all odd-positioned matches, and the second cleans up the remaining even-positioned ones that were left intact after the first pass.

2. TEXTSPLIT + TEXTJOIN (Excel 365/2021+)

If you have access to dynamic array functions, this method is more robust—it avoids pattern-matching issues entirely by processing each value individually:

=LET(
    original_str, A1,
    values_start, FIND("VALUES (", original_str) + 7,
    values_part, MID(original_str, values_start + 1, LEN(original_str) - values_start - 1),
    split_vals, TEXTSPLIT(values_part, ", "),
    processed_vals, IF(split_vals = "''", "NULL", split_vals),
    LEFT(original_str, values_start) & TEXTJOIN(", ", TRUE, processed_vals) & ")"
)

Here's what it does step-by-step:

• Locates the start of the values list after VALUES (
• Splits the values into individual items
• Replaces any '' with NULL
• Reassembles the full SQL statement

3. Custom VBA Function (For Frequent Use)

If you need to do this regularly, a VBA function will simplify things. Here's how to set it up:

1. Press Alt+F11 to open the VBA Editor
2. Insert a new module, then paste this code:

Function ReplaceEmptyWithNull(ByVal sqlStr As String) As String
    ' Handle all cases of empty strings in SQL values
    ReplaceEmptyWithNull = Replace(sqlStr, "('', '',", "(NULL, NULL,")
    ReplaceEmptyWithNull = Replace(ReplaceEmptyWithNull, ", '',", ", NULL,")
    ReplaceEmptyWithNull = Replace(ReplaceEmptyWithNull, ", ''", ", NULL")
End Function

3. Back in Excel, use it like any other function: =ReplaceEmptyWithNull(A1)
   This function covers edge cases like empty strings at the start, middle, or end of the values list.

内容的提问来源于stack exchange，提问作者Javier Arteagoitia