0
176
ViewsRegex to target different cells based on what it starts with and only replacing the first character IF it matches THEN - Google App Script
So I have a column in my spreadsheet that looks like the below image.
.
My ultimate goal is to create a script that removes the first symbol IF applicable else just get the value.
So far I was able to remove all the symbols in a cell but that's not my actual goal the code I've been using is the below.
function removePlus() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange('BK2:BK');
var textFinder = range.createTextFinder("+").matchFormulaText(true)
textFinder.replaceAllWith("");
I posted a question yesterday and was directed to look at regex. However, it appears with regex I have to put in the string of values I want to replace, but as you can see the values in the column vary. So I'm really not sure how to tackle this in a short time-frame. I have been reading up on resources but to no prevail. If possible detailed explanations or pointing me in the right direction will be helpful. I'm not looking for anyone to just tell me code, I really do want to learn on my own but I'm just not having any success. Thank you for the time.
2 answers
Answer question0
Something like this?
function removeFirstSymbol() {
const sheet = SpreadsheetApp.getActiveSheet()
const range = sheet.getRange(`BK2:BK`)
const values = range.getDisplayValues().flat()
const formatted = values.map(row => {
return (new RegExp(`[^A-Za-z0-9]`).test(row.charAt(0)))
? [row.slice(1)]
: [row]
})
range.setValues(formatted)
}
Commented:
// Get all values as they're displayed...
const values = range.getDisplayValues().flat()
// For each value...
const formatted = values.map(row => {
// If the first character is not a number or letter...
return (new RegExp(`[^A-Za-z0-9]`).test(row.charAt(0)))
// Remove the first letter,
? [row.slice(1)]
// Or keep as is.
: [row]
})
Note, this removes ALL symbols in the first character position, as I had looked at your previous post.
0
- Use
^[+*&%=]^Start of string[]Character class: Any of the characters inside[]:+,*,&,%and=
- Also use
useRegularExpressionsto use regex parsing
range.createTextFinder("^[+*&%=]")
.matchFormulaText(true)
.useRegularExpressions(true)
.replaceAllWith("")