Changing Roman numerals BACK to numbers.

[Futile Crush]

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?

Hope you can help.

 

[VoG]

Try this http://www.eggheadcafe.com/forumarchives/Excelprogramming/Sep2005/post24120930.asp

 

[Futile Crush]

Thanks for the link, but none of it made any sense . It seemed like VBA code too, of which I have no understanding.

Is anyone else able to help?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, from the Insert menu select Module then paste into the white space on the right

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-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;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

 

[sijpie]

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

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

 

[barry houdini]

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)

 

[Futile Crush]

@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!

 

[sijpie]

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.

 

[Futile Crush]

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

 

[katsumiblisk]

Hello from the future. We now have ARABIC(roman_numeral_string)