How to set part of string bold?

Question

I want to take a string from a cell and examine it for keywords. These keywords should then be marked Bold and the complete string should be returned with the Bold marked keywords. Here is an example:

=boldKeywords("I am an example text", "text")

Result: I am an example text.

The script works so far. I am just not possible to make the keywords bold. This should happen here: "//splitString[i] should be set bold". Here is the script:

function boldKeywords(text, keywords) {
    
// Split the test and the keywords into an Array with single Strings
  var splitString = text.split(" ");
  var splitKeywords = keywords.toUpperCase().split(", ");
    
//Go through the array and compare each word to the keywords
  for(var i = 0; i < splitString.length; i++){
    for(var j = 0; j < keywords.length; j++){
      if(splitString[i].toUpperCase().localeCompare(splitKeywords[j]) == 0){
        //splitString[i] should be set bold
      }
    } 
  }
     
//Concatenate Array Sting to one String
  var retunString = "";
          
  for(var j = 0; j < splitString.length; j++) {
    retunString = retunString + " " + splitString[j];
  }

//return String
  return retunString;
}

Answer 1

To make parts of a cell content bold, you need to use RichText

As mentioned by Marios, you cannot use RichText in a custom formula, so you have to modify your set-up a bit.

for example, you can write your key word(s) into a free cell (e.g. A1) and create a custom button to which you can assign the following script:

function boldKeywords() {
  var cell = SpreadsheetApp.getActive().getActiveSheet().getActiveCell();
  var text = cell.getValue();
  var keywords = SpreadsheetApp.getActive().getActiveSheet().getRange("A1").getValue();  
  // Split the test and the keywords into an Array with single Strings
  var splitString = text.split(" ");
  var splitKeywords = keywords.toUpperCase().split(", ");
  var value = SpreadsheetApp.newRichTextValue();
  value.setText(text);
  var position = 0;
  for(var i = 0; i < splitString.length; i++){
    for(var j = 0; j < splitKeywords.length; j++){
      if(splitString[i].toUpperCase().localeCompare(splitKeywords[j]) == 0){
        var start = text.indexOf(splitString[i], position)-1;
        var end = start + splitString[i].length+1;
        var bold = SpreadsheetApp.newTextStyle().setBold(true).build();
        value.setTextStyle(start, end, bold);       
      }
    } 
    position += splitString[i].length+1;
  }  
  var values = value.build();
  cell.setRichTextValue(values);
}

This script will mark all the keywords bold for the cell that is being selected at the moment you press on the button.

Note that if you expect to encounter more than once in a cell, it is important to define position to correctly highlight all instances of a keyword as bold.

Answer 2

Unfortunately, custom functions can not return formatted data.

This issue has been reported already in the IssueTracker. You can click on the star button to the top left of the page to increase the chances of this feature to be implemented by Google.

Potential workarounds:

• You can create a button/menu to execute a regular function that will be able to set the format and the value of the selected cell.
• You can use an onEdit() trigger to set the format and the value of the cell when you edit that cell or when you click on a checkbox for example.
• You can set up a time-driven trigger to change the format of the cell when the cell hasn't been formatted properly.