Google Sheets – Conditionally Substitute Cell Contents with Unicode

conditional formattinggoogle sheets

Is it possible to conditionally substitute a cells contents, with a Unicode character i.e. rather than colouring all '1' cells green, swap the '1' for another character instead?

Specifically, I want to exchange integers for 'Harvey Balls'.

For example, I have:

A | B | C | D

X | 1 | 2 | 1

Y | 2 | 4 | 1

Z | 3 | 2 | 4

I'd like the same table formatted as below (without duplication):

A | B | C | D

X | ◔ | ◕ | ◔

Y | ● | ● | ◔

X | ◕ | ◕ | ●

If it's any help, the substitute rule I have so far is: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F11, "0", char(9675)),"1", char(9684)), "2", char(9681)), "3", char(9685)), "4", char(9679))

I've also added the above tests to a publicly availably Google spreadsheet.

I don't mind – but would like to avoid – a duplicate column or table. But the latter option (typing values in a second sheet, that update renders first sheet with unicode characters) seems to be the only option to me…

Best Answer

This is similar in spirit to Convert all text to UPPERCASE in a Google Spreadsheet, so a similar script solution can be used. This script automatically replaces 0-4 by corresponding Harvey Balls in the spreadsheet to which it's bound. (It does not affect pre-existing cells, since it's triggered by edits.)

function onEdit(e) {
  var n = parseInt(e.value, 10);
  if (n == e.value && n >= 0 && n <= 4) {
    e.range.setValue(['○', '◔', '◑', '◕', '●'][n]);
  }
}

The check n == e.value is to avoid converting fractional values like 2.3.

One can also extend the condition with something like 

&& SpreadsheetApp.getActiveSheet().getName() == 'MySheet'

to restrict the automatic replacement to a particular sheet.

Related Topic