3

I am having trouble creating a script that will find the value 'MKE' when entered into column C, and replace it with 'Milwaukee, WI'.

Basically, I have addresses being placed into column C and would like to replace the end of the address with another value.

Is this possible?

1 Answer 1

1

If you highlight the column & press Ctrl-H you should be able to replace the string MKE how you want.

Before:

enter image description here

After:

enter image description here

Edit:
To auto fire this replacement:

In your sheet choose Tools|Script Editor from the menu & paste the code below which act on cells c1 to c10 on the sheet. The OnOpen method should fire when the sheet is opened, or can be fired directly from the script editor. I've not changed the UI to add the function to the menu, but that is also possible.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("c1:c10");
  var to_replace = "MKE";
  var replace_with = "Milwaukee, WI";
  replaceInSheet(sheet,range, to_replace, replace_with);
}

function replaceInSheet(sheet, range, to_replace, replace_with) {
  //Confirm
  var ui = SpreadsheetApp.getUi(); 
  var spread = SpreadsheetApp.getActiveSpreadsheet();

  var result = ui.alert(
     "Will update " + to_replace + " to " + replace_with + " ",
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    spread.toast("Will update " + to_replace + " to " + replace_with + " ", "ALERT");

    var data  = range.getValues();

    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var row=0; row<data.length; row++) {
      for (var item=0; item<data[row].length; item++) {
        oldValue = data[row][item];
        newValue = data[row][item].replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[row][item] = newValue;
        }
      }
    }
    range.setValues(data);
    spread.toast(cellsChanged + " cells changed", "STATUS");
  }
  else {
    // User clicked "No" or X in the title bar.
    spread.toast("No action taken", "ABANDONED");
  }
}
4
  • Please explain briefly how to use the proveided code and consider to add a description about how it works.
    – Rubén
    Mar 20, 2017 at 19:38
  • 1
    @Ruben Not a problem I've added some details. TBH this stackexchange seems a bit dead - lots of similar questions are on stackoverflow & are not being transferred. Is it worth persisting with WebApps?
    – amelvin
    Mar 21, 2017 at 9:28
  • Each site has it's own scope/guidelines/community/activity level. Have you taking a look to stackexchange.com/sites? There you could fin the broad stats for all the sites in the network. By the other hand, what do you mean by "are not being transferred"? By the way, usually it's not necessary to add "EDIT", "UPDATE" or something like that, as at the bottom of the page is the link "edited x time unit ago", that takes the user to the revision history.
    – Rubén
    Mar 21, 2017 at 10:05
  • Thank you amelvin. Nice job.
    – eyyo
    Mar 5, 2019 at 15:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.