Conditionally substitute cell contents with Unicode characters

Question

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...

Answer 1

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.