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.

CodePudding user response：

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.

CodePudding user response：

• Use ^[ *&%=]
  • ^ Start of string
  • [] Character class: Any of the characters inside []: ,*,&,% and =

• Also use useRegularExpressions to use regex parsing

range.createTextFinder("^[ *&%=]")
    .matchFormulaText(true)
    .useRegularExpressions(true)
    .replaceAllWith("")