CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Joe
Joe

Reputation: 37

Last part of the string after the last delimiter - Google Apps Script

The spreadsheet I am working with has a lot of incorrect data, but for addresses, in most cases the postcode is at the end, but it is not always the case. Below is a sample of example records.

address
11 Street; City; Area; AB1 2CD
12 Avenue; AB2 3CD
AB3 4CD
SW3
London AB3 2CD
Town
AB5 6CD; gb
n/a

Desired result

postcode
AB1 2CD
AB2 3CD
AB3 4CD
AB3 2CD
AB5 6CD

I am aware that there isn't a consistent way to retrieve everything, but it tends to be the last part of the string split by the last semicolon, so a helpful script would allow me to retrieve the last part of the string until the last semicolon.

Upvotes: 0

Views: 595

Answers (1)

Cooper
Cooper

Reputation: 64110

function strip() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1).getDisplayValues().flat();
  let vo = vs.map(s => [s.match(/\b[0-9A-Za-z/]+$/gi)[0]]);
  sh.getRange(2,2,vo.length,1).setValues(vo);
}

Output:

address
11 Street; City; Area; AB1 2CD 2CD
12 Avenue; AB2 3CD 3CD
AB3 4CD 4CD
SW3 SW3
London AB3 2CD 2CD
Town Town
AB5 6CD; gb gb
n/a n/a

Upvotes: 1

Related Questions