如何在Google Sheet指定范围中批量替换子串（+号）

Got it, let's tackle this problem step by step. Since you can't do a global replace (other columns have + signs that need to stay), we have three targeted solutions specifically for the C2:C100001 range:

1. Quick Find & Replace (Recommended for Most Cases) #

This is the fastest method—Google Sheets lets you limit find/replace to a specific range, and regex will ensure we only target trailing + signs:

1. Select the range C2:C100001 (click C2, scroll down to C100001, hold Shift and click to select the entire range).
2. Press Ctrl+H (Windows/Linux) or Cmd+H (Mac) to open the Find and Replace dialog.
3. In the Find field, enter +$ (the $ tells regex to only match the + at the end of the cell value).
4. Leave the Replace with field empty.
5. Check the box for Use regular expressions (this is critical—otherwise it won't work as intended).
6. Click Replace all.

This will instantly strip any trailing + from only the cells you selected, leaving other columns untouched.

2. Formula-Based Approach (No Scripts Needed) #

If you prefer a non-regex, formula-driven method, use an auxiliary column to clean the values first, then paste them back:

1. Insert a new column next to C (e.g., column D).
2. In cell D2, enter one of these formulas:
   • Simple string manipulation (works for single trailing +):

     =IF(RIGHT(C2, 1)="+", LEFT(C2, LEN(C2)-1), C2)
     

   • Regex version (more robust if you might have accidental extra spaces, though your example shows clean values):

     =REGEXREPLACE(C2, "\+$", "")
     

3. Drag the fill handle (small square at the bottom-right of D2) all the way down to D100001 to apply the formula to the entire range.
4. Select the entire D column range (D2:D100001), copy it (Ctrl+C/Cmd+C).
5. Right-click cell C2, select Paste special > Values only to overwrite the original values with the cleaned ones.
6. Delete the auxiliary D column if you don't need it anymore.

3. Google Apps Script (For Extra Large Datasets) #

If you're dealing with 100k+ rows and find the formula drag slow, a script will process the data in bulk:

1. Open your Google Sheet, click Extensions > Apps Script to open the script editor.
2. Delete any default code, then paste this:

   function removeTrailingPlus() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     const range = sheet.getRange("C2:C100001");
     const values = range.getValues();
     
     // Loop through each cell and strip trailing + if present
     const updatedValues = values.map(row => {
       const cellValue = row[0];
       if (typeof cellValue === 'string' && cellValue.endsWith('+')) {
         return [cellValue.slice(0, -1)];
       }
       // Return original value if no trailing +
       return row;
     });
     
     // Write cleaned values back to the range
     range.setValues(updatedValues);
   }
   

3. Click the run button (▶️), authorize the script when prompted (you'll need to allow access to your sheet).
4. Once the script finishes, all trailing + signs in C2:C100001 will be removed.

All three methods will leave your other columns' + signs intact—pick the one that fits your comfort level!

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