L_ROMAN2INT

=L_ROMAN2INT(text)

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.

/**
* 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)
        )
    ))))
));

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

L_ROMAN2INT Examples

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"

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