Replace all Numbers with Roman Numeral in Google Sheets

Question

Level of skill: None - searching as I go to try and figure it out.

I currently have a Google sheet in which I am trying to automatically convert all numbers (1,2,3...) to roman numerals (I,II,III, etc).

I do have a very lengthy work around after having tested with recording small macros and pasting them all together. See example below: spreadsheet.getRangeList(['E1:E2']).setFormula('=IFERROR(ROMAN(D1;0);"")'); This sets the roman value next to the cell with the number. Google sheets does auto update when shifting down for cells so that works.. Which I then have it cut and paste the value of in the original cell. Rinse repeat for every range on the sheet that contains numbers.

This feels extremely clunky to me and must be a better way. Either by removing the cut/paste section and store the value as part of the function, or simply check each in the sheet, if a number, apply function, if not, move on... or any other method that would work well.

For clarification, there are empty spots/areas where there is no data.

Example:

A	B	D	F	H
1	Apple	3	Potato	5
2	Pear	1
3			Celery	0
4	Carrot	2	Tomato	4

Desired Output

A	B	D	F	H
1	Apple	III	Potato	V
2	Pear	I
3			Celery	0
4	Carrot	II	Tomato	IV

Any guidance/ideas would be much appreciated. Even if it is just a link to what functions/info to go read up on as I don't mind the journey and learning.

user_14606052 · Answer

Try this:

function convertToRomanNumeral() {
  //this will get the range of the data. Example: A1:G4
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  //this will return 2 dimensional arrays of values
  var data = range.getValues();
  for( var i = 0; i < data.length; i++ ){
    for( var j = 0; j < data[0].length; j++ ){
      //convert number to roman numeral
      var val = romanize(data[i][j]);
      if(val){
        //change the value of array element if the value if val is not empty. 
        data[i][j] = val;
      }
    }
  }
  //write the changes to sheet
  range.setValues(data);
}


function romanize (num) {
    if (isNaN(num))
      return '';
    else if (parseInt(num) == 0)
      return 0;
    var digits = String(+num).split(""),
        key = ["","C","CC","CCC","CD","D","DC","DCC","DCCC","CM",
               "","X","XX","XXX","XL","L","LX","LXX","LXXX","XC",
               "","I","II","III","IV","V","VI","VII","VIII","IX"],
        roman = "",
        i = 3;
    while (i--)
        roman = (key[+digits.pop() + (i * 10)] || "") + roman;
    return Array(+digits.join("") + 1).join("M") + roman;
}

Example:

Before running the script:

After running the script:

Reference:

Admin · Answer

Open a column next to it and set something like this all the way down it

=IFS(ISNUMBER(A1), ROMAN(A1), NOT(ISNUMBER(A1)), "")

You can always hide the original column if it is bothering you, or even better do a QUERY function to only bring over the data you actually want to see into another tab/sheet.

Replace all Numbers with Roman Numeral in Google Sheets

2 Answers