Bold specific text in Google Sheets

Question

The following VBA code bolds cells that contain a specific phrase. Is there something that i can use in Google Sheets that will have similar output?

Sub FindAndBold()
Dim I As Long
Dim xFind As String
Dim xCell As Range
Dim xTxtRg As Range
Dim xCount As Long
Dim xLen As Integer
Dim xStart As Integer
Dim xRg As Range, xRgFind As Range
Dim xTxt As String
Dim xArr() As String
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("Please select data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
On Error Resume Next
Set xTxtRg = Application.Intersect(xRg.SpecialCells(xlCellTypeConstants, xlTextValues), xRg)
If xTxtRg Is Nothing Then
MsgBox "There are no cells with text"
Exit Sub
End If
Set xRgFind = Application.InputBox("Select the text cells you want to bold", "Kutools for Excel", , , , , , 8)
If xRgFind Is Nothing Then
MsgBox "No text was listed", vbInformation, "Kutools for Excel"
Exit Sub
End If
ReDim xArr(xRgFind.Count - 1)
For I = 0 To (xRgFind.Count - 1)
xArr(I) = xRgFind(I + 1)
Next
For Each xCell In xTxtRg
For I = 0 To UBound(xArr)
xFind = Trim(xArr(I))
xStart = InStr(xCell.Value, xFind)
xLen = Len(xFind)
Do While xStart > 0
xCell.Characters(xStart, xLen).Font.Bold = True
xCount = xCount + 1
xStart = InStr(xStart + xLen, xCell.Value, xFind)
Loop
Next
Next
If xCount > 0 Then
MsgBox "number of " & CStr(xCount) & " text be bolded!", vbInformation, "Kutools for Excel"
Else
MsgBox "Not find the specific text!", vbInformation, "Kutools for Excel"
End If
End Sub

For example, lets have text in cell B1 "Mobile phone A5225" Bud i want to bold only one specific word from this cell, lets say "phone" so output will be: "Mobile phone A5525" List of words to bold will be A1:A50

Answer 1

You can use RichTextValueBuilder.
Here's a sample based on your VBA script.

function FindAndBold(){
  var ui = SpreadsheetApp.getUi();
  var search = ui.prompt("Input the text you want to bold").getResponseText();
  var rangeStr = ui.prompt("Input the target range (e.g. A1:C6)").getResponseText();
  var range = SpreadsheetApp.getActive().getRange(rangeStr);
  var values = range.getValues();
  var bold = SpreadsheetApp.newTextStyle().setBold(true).build();
  var count = 0;

  for ( var i = 0; i < values.length; i++ ){
    for ( var j = 0; j < values[i].length; j++ ){
      var value = values[i][j];
      var newValue = null;
      var start = value.indexOf(search);
      while( start >= 0 ){
        if(newValue == null){
          newValue = SpreadsheetApp.newRichTextValue().setText(value.toString());
        }
        newValue.setTextStyle(start, start + search.length, bold);
        start = value.indexOf(search, start + search.length);
        count++;
      }

      if(newValue != null){
        var cell = range.getCell(i+1, j+1);
        cell.setRichTextValue(newValue.build());
      }
    }
  }
  if( count > 0 ){
    ui.alert("number of " + count + " text be bolded!");
  }
  else{
    ui.alert("Not find the specific text!");
  }
}

Hope this helps.

Answer 2

That can certainly be done in Google Apps Script, but it would be a lot simpler to just use the built-in conditional formatting feature.

To highlight cells where the phrase in cell A1 appears as part of the Apply to range cells, use a conditional formatting rule like this:

Text contains =$A$1

Conditional formatting can only format whole cells. To highlight partial phrases within cells, use this Apps Script function:

/**
* Formats text strings so that a chosen phrase is bold blue.
* Handles multiple occurrences of the phrase in each cell.
* Ignores cells with numeric values such as 123.45 or 10/28/2021.
* Overwrites formulas with the values as shown in the cell.
*/
function formatPhraseInText() {
  // version 1.2, written by --Hyde, 28 October 2021
  //  - see https://stackoverflow.com/a/69750237/13045193
  const range = SpreadsheetApp.getActiveRange();
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter the text you want to bold in ' + range.getA1Notation() + ':');
  const text = response.getResponseText();
  if (response.getSelectedButton() !== ui.Button.OK || !text) {
    return;
  };
  const regex = new RegExp(text.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'), 'gi');
  const format = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('blue').build();
  const values = range.getDisplayValues();
  let match;
  const formattedText = values.map(row => row.map(value => {
    const richText = SpreadsheetApp.newRichTextValue().setText(value);
    while (match = regex.exec(value)) {
      richText.setTextStyle(match.index, match.index + match[0].length, format);
    }
    return richText.build();
  }));
  range.setRichTextValues(formattedText);
}

To add a custom menu item to run the function, add an onOpen(e) function and reload the spreadsheet:

/**
* Simple trigger that runs each time the user opens the
* spreadsheet.
*
* Adds a menu item to highlight a phrase.
*
* @param {Object} e The onOpen() event object.
* @OnlyCurrentDoc
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Highlight')
    .addItem('Highlight a phrase', 'formatPhraseInText')
    .addToUi();
}

To use regular expressions to match more complex patterns, replace the const regex line with this:

  const regex = new RegExp(text, 'gi');

Google Apps Script uses JavaScript regular expressions.