≡ ▼
=L_ROMAN2INT(text)
ArgumentDescriptionExample
textA text-based Roman numeral such as XLII or xliiXLII

Download the Template

In the template file, navigate to the General worksheet to see the L_ROMAN2INT function in action.

Description

The ROMAN function in Excel can convert an integer such as 42 to the Roman numeral XLII (up to 3999). The L_ROMAN2INT function can convert a Roman numeral back into an integer. The Roman numeral text may be lowercase or uppercase. It also works with text being an array of Roman numerals.

The L_ROMAN2INT first converts a text string to an array of individual characters. Each of the individual characters is then converted to the corresponding integer value using XLOOKUP and the following built-in arrays:

{"I", "V", "X", "L", "C", "D",  "M"},
{  1,   5,  10,  50, 100, 500, 1000},

The integer values are then added together with the rule that if any value is smaller than the one immediately after it, it is subtracted instead of added. For example "IX" would be -1+10=9 and "CDXL" would be -100+500-10+50=440.

Important: Only a single smaller digit preceding a larger one will be subtracted. The user is required to ensure that the Roman numeral is valid. For example, "IIX" is not a valid classic Roman numeral even though the algorithm in the L_ROMAN2INT function would return the value 10 (1 + -1 + 10).

This function has been tested for all values 1 through 3999 resulting from ROMAN(value,form) where form is 0, 1, 2, 3 or 4.

L_ROMAN2INT was originally created to enable the automation of Roman numeral outlining in Excel. For example, if a previous item was labeled as IV, then the next item would be V. The following formula adds 1 to a Roman numeral text value then returns the value as a Roman numeral:

=ROMAN(L_ROMAN2INT("IV")+1))

Result: "V"

Lambda Formula

This code for using L_ROMAN2INT in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code for AFE Workbook Module (Excel Labs Add-in)


/**
* Converts a Roman numeral to its integer value: IV = 4, XII = 12, etc.
*/
L_ROMAN2INT = LAMBDA(text,
LET(doc,"https://www.vertex42.com/lambda/roman2int.html",
    MAP(text,LAMBDA(cell,LET(
        strarray,TRANSPOSE(MID(UPPER(cell),SEQUENCE(LEN(cell),1),1)),
        roman_char,{"I","V","X","L","C","D","M"},
        values,{1,5,10,50,100,500,1000},
        strvalues,XLOOKUP(strarray,roman_char,values,"#Error: Invalid Roman Numeral"),
        IF(LEN(cell)=1,strvalues,
            SUM(strvalues*HSTACK(2*(DROP(strvalues,,1)<=DROP(strvalues,,-1))-1,1)
        )
    ))))
));

Named Function for Google Sheets

Note: Requires the L_DROP function

Name: L_ROMAN2INT
Description: Convert a Roman numeral like XLII to a decimal integer
Arguments: text
Function:

=LET(doc,"https://www.vertex42.com/lambda/roman2int.html",
    MAP(text,LAMBDA(cell,LET(
        strarray,ARRAYFORMULA(TRANSPOSE(MID(UPPER(cell),SEQUENCE(LEN(cell),1),1))),
        roman_char,{"I","V","X","L","C","D","M"},
        values,{1,5,10,50,100,500,1000},
        strvalues,ARRAYFORMULA(XLOOKUP(strarray,roman_char,values,"#Error: Invalid Roman Numeral")),
        ARRAYFORMULA(IF(LEN(cell)=1,strvalues,
            SUM(strvalues*HSTACK(2*(L_DROP(strvalues,,1)<=L_DROP(strvalues,,-1))-1,1)
        ))
    ))))
)
Warning
These L_ROMAN2INT functions are not compatible between Excel and Google Sheets.

L_ROMAN2INT Examples

Example 1
Add 24 to the Roman numeral "DXLVI" and return the new Roman numeral. How: L_ROMAN2INT is used to convert to an integer, then the values are added, and the result is converted back to a Roman numeral.
Test: Copy and Paste this LET function into a cell
=LET(
    roman_text, "DXLVI",
    value_to_add, 24,
    ROMAN( L_ROMAN2INT(roman_text) + value_to_add )
)

Result: "DLXX"

Tip: ROMAN always returns the uppercase value. Use LOWER to convert it to lowercase.

Example 1
Test: Copy and Paste this LET function into a cell
=LET(
    values, SEQUENCE(3999),
    form, 0,
    numerals, ROMAN(values,form),
    ret_values, L_ROMAN2INT(numerals),
    number_of_errors, SUM(--(ret_values<>values)),
    number_of_errors
)

Result: 0
References & Resources
Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.