CODEWITHSUNDEEP
regexgoogle-sheetsreplace
reschultzed
reschultzed

Reputation: 33

Bulk find-and-replace regexs in Google Sheets

Is there a function, script, or add-on that can apply a large series of regex replacements to a range of data in Google Sheets? I have one sheet with a list of addresses and another with several dozen pairs of regular expressions in two columns (e.g. "St.$" and "Street"), and I want to replace all instances of the first column of phrases in the address list with the corresponding phrase in the other.

This question was initially closed as being answered by this, but even after some significant tweaking to fit my situation, I can only get that formula to replace one phrase per address, and any other matching phrases will be replaced by the first word (so "1234 N. Main St." becomes "1234 North Main North" instead of "1234 North Main Street"). Is there a method that specifically doesn't do that?

Upvotes: 2

Views: 225

Answers (2)

xyz333
xyz333

Reputation: 768

Possibly this:

values = sheet.getDataRange().getDisplayValues()
values = values.map(outer => outer.map(inner => inner
                                            .replaceAll(/\bN\./g, 'North')
                                            .replaceAll(/\bS\./g, 'South')
                                            .replaceAll(/\bSt\./g, 'Street')
                                       )
                                      );

Change this values = sheet.getDataRange().getDisplayValues() to what ever you need

Upvotes: 0

General Grievance
General Grievance

Reputation: 4987

Assuming all your data is in a single column, here's a script (much cleaner and more extensible than the formula approach):

Note: this is not an in-place replacement.

function processColumn(column)
{
  // Add more as needed:
  // [Regex, Replacement]
  let replaceTable = 
  [
    [/\bN\./g, 'North'],
    [/\bS\./g, 'South'],
    [/\bSt\./g, 'Street']
  ];

  // A column is an array of rows
  // A row is an array of values.
  return column.map(row => 
    // This is an optimization to skip over blank values
    row[0] 
      ? replaceTable.reduce(
        // Replace one value at a time, in order
        (curString, tableEntry) => curString.replace(tableEntry[0], tableEntry[1]),
        row[0]
      )
      : ''
  );
}

Usage Example:

All your data is in A:A, like so:

A
123 N. Main St.
124 N. Main St.
19 S. Main St.

Then in column B:

=processColumn(A:A)

This will place all the processed values in column B:

B
123 North Main Street
124 North Main Street
19 South Main Street

Upvotes: 3

Related Questions