如何编写公式或函数在单个单元格中替换多个指定字符（含特殊字符转义示例）

Hey there! Let's break down your two Excel formula questions clearly—both are about replacing multiple characters in a single cell, which is a common task for things like URL formatting or text standardization.

If you need to swap out several different letters (or characters) in a single cell, the go-to tool is Excel's SUBSTITUTE function, nested for each replacement you need.

Here's how it works: Each SUBSTITUTE handles one pair of "old character → new character", and you nest them so the result of the first replacement feeds into the next.

Example #

Suppose cell A1 contains "apple banana cherry", and you want to replace "a" with "x", "b" with "y", and "c" with "z". Your formula would be:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "a", "x"), "b", "y"), "c", "z")

This gives you "xpple yxnana zherry"—each replacement happens in sequence, so you can chain as many as you need.

For your specific request—swapping spaces for %20, single quotes for %27, and plus signs for %2B—you'll use the same nested SUBSTITUTE approach, tailored to your target characters.

Direct Formula (No VBA) #

Put this in any cell to process the content of A1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", "%20"), "'", "%27"), "+", "%2B")

Let's break it down step by step:

• The innermost SUBSTITUTE(A1, " ", "%20") replaces all spaces with %20
• That result gets passed to the next SUBSTITUTE(..., "'", "%27") which swaps every single quote ' for %27
• Finally, the last SUBSTITUTE(..., "+", "%2B") replaces all plus signs + with %2B

Bonus: Custom VBA Function (For Repeated Use) #

If you need to do this often, a custom VBA function will save you from typing the long nested formula every time. Here's how to set it up:

1. Press Alt + F11 to open the VBA Editor
2. Insert a new module (Right-click your workbook in the Project pane → Insert → Module)
3. Paste this code:

Function URLCharReplace(cell As Range) As String
    Dim tempText As String
    tempText = cell.Value
    ' Apply each replacement in order
    tempText = Replace(tempText, " ", "%20")
    tempText = Replace(tempText, "'", "%27")
    tempText = Replace(tempText, "+", "%2B")
    URLCharReplace = tempText
End Function

4. Close the VBA Editor
   Now you can use this function like any built-in Excel function:

=URLCharReplace(A1)

It does exactly the same thing as the nested formula, but it's cleaner and easier to reuse across your workbook.

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