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.

Nikko J. · Accepted Answer · 2021-03-18T18:01:23.377

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:

Ugh. The things I could do if appscript wasn't blocked at work. This is a really great solution. — Travis, Mar 18 '21 at 18:35
This worked flawlessly and is exactly what I was trying to figure out. Very clean solution that scales regardless of size of document. Rather than me trying to manually set and copy each range. Very much appreciated. Now to learn how this wizardry works :) — SystemOverload, Mar 18 '21 at 19:04

score 0 · Answer 2 · 2021-03-18T16:18:01.753

0

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.

edited Mar 18 '21 at 16:18

answered Mar 18 '21 at 16:12

1

Thank you for the swift reply - unfortunately going fully down the column doesn't quite work as there is merged text fields in between above the data. Ideally the Roman value would be returned in the original cell (layout of the overall document is important). The user would be entering in the data in non Roman numerals, hence not being able to hide the column and preference for not a separate tab/sheet – SystemOverload Mar 18 '21 at 16:57
1

That is EXTREMELY difficult to pull off. Can you remake the document on another tab without the merged cells and use that as a sort of data tab that the end user can populate, so that you can use something like the query function to display the data on another tab as a sort of dashboard? – Mar 18 '21 at 17:05
1

I can, though the goal is for there to be a single landing page. Just to clarify as well, this is literally just for aesthetic purposes as well. The user could just type the Roman numeral.. highest required number is 10.. so really not that major. I do have it working, the code is just bloated and inefficient. I did improve on it by using the if statement for validation of a number which cuts down on the amount of errors that needed to be hidden. So thank you for that :) – SystemOverload Mar 18 '21 at 17:53

Replace all Numbers with Roman Numeral in Google Sheets

2 Answers2