Google Sheets formula that changes text only between parenthesis

user_19560510 0
Viewed 41

I'm trying to format a sheet that creates a grid between names; the names are automatically mirrored on one axis using the = link method so that it's user-friendly.

However, linking cells via = doesn't copy any formatting and only uses the native formatting of the cell. I want half of the text to be a different color (lighter grey and not bold, ideally). Since you cannot adjust the text of a mirrored cell and it doesn't mirror formatting, I was thinking I could backdoor it through a formula.

The wise thing would be to either swallow that it doesn't work that way and go with the uglier formatting, or to unlink them and simply allow the user to manually enter the data for neatness. However, I figured it'd be a good learning exercise if I could figure out a way to selectively format only text within parentheticals.

Is there a way to use a formula to color text only within parenthesis?

google-sheets-formula google-sheets
1 Answers

To format the text in parentheses, try

function formatParenthesis() {
  const range = SpreadsheetApp.getActiveRange()
  const spec = { regex: /\(.*\)/gi, textColor: 'grey', bold: true, italic: true };
  const values = range.getDisplayValues();
  let match;
  const formattedText = values.map(row => row.map(value => {
    const richText = SpreadsheetApp.newRichTextValue().setText(value);
    const format = SpreadsheetApp.newTextStyle()
      .setForegroundColor(spec.textColor)
      .setBold(spec.bold)
      .setItalic(spec.italic)
      .build();
    while (match = spec.regex.exec(value)) {
      richText.setTextStyle(match.index, match.index + match[0].length, format);
    }
    return richText.build();
  }));
  range.setRichTextValues(formattedText);
}

references

enter image description here

user_150193800
Related