2

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

  • There is no conditional formatting with Harvey balls in Google Sheets (like Excel has). So the only way to have ◔ shown in place of 1 is to replace 1 with ◔ , in which case the number is gone; in particular you will not be able to calculate with this data. Is this acceptable? – user79865 Nov 30 '15 at 15:51
  • @NormalHuman I don't need to run calculations but I do want it to update automatically. Would your technique still work if I went back and changed what was a 1, to a 2? If so, that's what I'm after. – Jon Hadley Nov 30 '15 at 16:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.