CODEWITHSUNDEEP
google-apps-script
Adrianne K Seiden
Adrianne K Seiden

Reputation: 35

Replace with lowercase in RE2

I had a google sheets spreadsheet with addresses in all caps. I used proper() to fix that, but now I have several numbered streets that come out like 2Nd or 4Th. I wanted to correct these using regular expressions in find/replace so I searched for ([1-9])([N,R,S,T]) and put $1\l$2 in the replace field. This didn't work because RE2 doesn't support \l. Can anyone help me replace uppercase letters with lowercase in google sheets? I couldn't find any equivalent to \l in the RE2 expressions list.

Upvotes: 1

Views: 623

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

A couple days ago I invented (reinvented, I'm sure) the funny algorithm which I hope can help you:

var s = 'Aaa, 2Nd bbb 4Th aaa BBB';

s.match(/\d[NRST]/g).forEach(t=>s=s.split(t).join(t.toLowerCase()));

console.log(s); // 'Aaa, 2nd bbb 4th aaa BBB'

The concept is as follows:

s.match(/####/g).forEach(t=>s=s.split(t).join(t.****));

s -- your string

#### -- your RegExp

**** -- any function which be apply on the fragments matched your RegExp

For GAS it could be something like this:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  const data = range.getValues().map(row => 
    row.map(s => {
      s.match(/d[NDTS]/g).forEach(t => 
      s = s.split(t).join(t.toLowerCase()));
      return s;
    }
  ));

  range.setValues(data);  
}

Upvotes: 2

Related Questions