How to simplify this google sheets regex sequence?-CodePudding

I want to make the following transformation to a set of datas in my google spreadsheets :

6 views -> 6
73K views -> 73000
3650 -> 3650
163K views -> 163000
1.2K views -> 1200
52.5K -> 52500

All the datas are in a column and depending on the case I need to apply a specific transformation. I tried to put all the regex in one formula but I failed. I always had a case over two regular expressions etc.

Anyaway I end up making these regex one case by one case in different columns. It works fine but I feel like it could slowdown the sheet since I except a lot of data coming into this sheet.

Here is the sheet : spreadsheet

Thank you for your help !

CodePudding user response：

Use regexreplace(), like this:

=arrayformula( 
  iferror( 1 / 
    value( 
      regexreplace( 
        regexreplace(trim(A2:A), "\s*K", "e3"), 
        " views", "" 
      ) 
    ) 
  ^ -1 ) 
)

See your sample spreadsheet.

CodePudding user response：

replace 'views' using regex: /(?<=(\d*\.?\d \K?)) views/gi To replace 'K' with or without decimal value, first, detect K then replace K with an empty string and multiply by 1000.

use call back function as:

txt.replace(/(?<=(\d*\.?\d \K?)) views/gi, '').replace(/(?<=\d)\.?\d K/g, x => x.replace(/K/gi, '')*1000)

code:

arr = [`6 views`,
`73K views`,
`3650`,
`163K views`,
`1.2K views`,
`52.5K`];


arr.forEach(txt => {
  console.log(txt.replace(/(?<=(\d*\.?\d \K?)) views/gi, '').replace(/(?<=\d)\.?\d K/g, x => x.replace(/K/gi, '')*1000))
})

Output:

CodePudding user response：

Say your inputs are in column A. Empty cells allowed. In any other column,

=arrayformula(if(A2:A<>"",value(substitute(substitute(A2:A," views",""),"K","e3")),))

works.

Adjust the range A2:A as needed.

Also note that non-empty cells with empty strings are ignored.

Basically, since Google Sheet's regex engine doesn't support look around, it is more efficient to take advantage of the rather strict patterns in your application and use substitute() instead.