Changing Roman numerals BACK to numbers.

Futile Crush

New Member
Joined
Oct 3, 2009
Messages
45
The title pretty much explains what I'm trying to do. Because I'm really not sure how to go about changing roman numerals back to numbers without the use of a VLOOKUP, in essence, I want to try and achieve something where the original numbers are NOT on the spreadsheet, just the numerals. Is there some sort of formula like EVAL, FORMULATEXT or something like that that I need to use? :confused:

Hope you can help.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Thanks for the link, but none of it made any sense :confused:. It seemed like VBA code too, of which I have no understanding.

Is anyone else able to help?
 
Upvote 0
Press ALT + F11 to open the Visual Basic Editor, from the Insert menu select Module then paste into the white space on the right

Code:
Function Arabic(Roman)
Dim Arabicvalues() As Integer, convertedvalue As Long, currentchar As String * 1
Dim i As Integer, message As String, numchars As Integer
Roman = LTrim(RTrim(Roman))
numchars = Len(Roman)
    If numchars = 0 Then    'if arg is null, we're outta here
    Arabic = ""
Exit Function
End If
ReDim Arabicvalues(numchars)
For i = 1 To numchars
    currentchar = Mid(Roman, i, 1)
    Select Case UCase(currentchar)
        Case "M": Arabicvalues(i) = 1000
        Case "D": Arabicvalues(i) = 500
        Case "C": Arabicvalues(i) = 100
        Case "L": Arabicvalues(i) = 50
        Case "X": Arabicvalues(i) = 10
        Case "V": Arabicvalues(i) = 5
        Case "I": Arabicvalues(i) = 1
        Case Else
        Arabic = "Sorry, " & Roman & " is not a valid Roman numeral!  "
        Exit Function
    End Select
Next i
For i = 1 To numchars - 1
    If Arabicvalues(i) < Arabicvalues(i + 1) Then
        Arabicvalues(i) = Arabicvalues(i) * -1
    End If
Next i
For i = 1 To numchars
    Arabic = Arabic + Arabicvalues(i)
Next i
End Function

Press ALT + Q to return to your sheet. You now have a User Defined Function that you can use just like a regular Excel function:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">F</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >MMIX</td><td style="text-align:right; ">2009</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=arabic(E1)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 
Upvote 0
VOG's reply doesnt take account of the substracting issue with IV or XC

Put this function in a module, and then you can call it from a cell like
=roman2num("MMMXCIV")

Code:
Function Roman2Num(Roman As String) As Long
    Dim Roman2 As String, Char1 As String, Char2 As String
    Dim Number As Long
    
    Roman2 = UCase(Roman)
    
    Do While Len(Roman2)
        Char1 = Left(Roman2, 1)
        Char2 = Mid(Roman2, 2, 1)
        Roman2 = Right(Roman2, Len(Roman2) - 1)
        Select Case Char1
            Case "M"
                Number = Number + 1000
                
            Case "D"
                Number = Number + 500
            Case "C"
                Select Case Char2
                    Case "M"
                        Number = Number + 900
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "D"
                        Number = Number + 400
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case Else
                        Number = Number + 100
                        
                End Select
            Case "L"
                Number = Number + 50
            Case "X"
                Select Case Char2
                    Case "M"
                        Number = Number + 990
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "D"
                        Number = Number + 490
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "C"
                        Number = Number + 90
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "L"
                        Number = Number + 40
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case Else
                        Number = Number + 10
                End Select
            Case "V"
                Number = Number + 5
            Case "I"
                Select Case Char2
                    Case "M"
                        Number = Number + 999
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "D"
                        Number = Number + 499
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "C"
                        Number = Number + 99
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "L"
                        Number = Number + 49
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "X"
                        Number = Number + 9
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case "V"
                        Number = Number + 4
                        Roman2 = Right(Roman2, Len(Roman2) - 1)
                    Case Else
                        Number = Number + 1
                End Select
        End Select
    Loop
    Roman2Num = Number
End Function
 
Upvote 0
If you have a Roman numeral up to 3999 in cell A1 try this formula to convert in B1

=MATCH(A1,INDEX(ROMAN(ROW(INDIRECT("1:4000"))),0),0)
 
Upvote 0
@Sijpie
I appreciate the effort you put into finding an answer, but I'm a little disappointed - that there's no simple formula-based method of doing it. I really don't know the first thing about VBA code, it sounds more complex than JavaScript and I know even less about that.

@Barry Houdini
Excelisfun told me all about you ^_^. Thanks a bunch for the answer!
 
Upvote 0
Nice one Barry Houdini! Excellent way to reverse the Excel roman() function.

Futile Crush, Barry's formula is probably as close as you'l get to someting simple. What it does is check the entry against the list the roman() function will generate (from 1 to 4000), and the return that number.

With my function (and other VBA code) you basically create your own excel function. And you can then use it like any other excel function. The only drawback is that the spreadsheet wil ask if you want to run macros.
 
Upvote 0
Futile Crush, Barry's formula is probably as close as you'l get to someting simple. What it does is check the entry against the list the roman() function will generate (from 1 to 4000), and the return that number.

With my function (and other VBA code) you basically create your own excel function. And you can then use it like any other excel function. The only drawback is that the spreadsheet wil ask if you want to run macros.

I see. Once more, a lot of thanks for trying to help out, but VBA code is meaningless to me, and is the equivalent of you posting an answer in Chinese. I'll try out Houdini's formula :)
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,919
Latest member
krismmmitch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top