Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Roman Numerals (Excel 2000)

    I would like to be able to format a column to display the value of it's cell content as roman numeral, so that it reads II, III, IV, etc. The underlying data needs to be in ordinary 'arabic' integer form, but I need to display the number as roman - in the same way as some people put the month in RNs to avoid date confusion ie 4/iv/05. Can we do this in Excel? I looked at format cells but could not see a way. Grateful for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Roman Numerals (Excel 2000)

    There is no roman format, but you can use the ROMAN worksheet function to display roman numbers in an adjacent column, and hide the column with original data.

    Say that your numbers are in column A, starting in A2 (with a column header in A1). In B1, enter an appropriate caption, and in B2, enter the formula
    =ROMAN(A2)
    Fill down as far as needed. You can hide column A now. You would still refer to the cells in column A for calculations; the values in column B are for display only.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Roman Numerals (Excel 2000)

    Excel has a great function

    =ROMAN(5), this will return V

    I have just put a list of numbers from 1 to 100 in column A and put the function in B, it works a treat.

    Hope this helps
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Roman Numerals (Excel 2000)

    Thanks to both of you. It is as I had feared - I will have to work around using two cols and the =roman() function. Another one for the wishlist!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Roman Numerals (Excel 2000)

    If you want to avoid "date confusion" why not format to a date using "mmm" instead of "mm":
    format - cells - Number(tab) custom:
    dd/mmm/yy

    it will not be a roman, but it will remain a date (and to me, be more "understandable") and not cause confusion.

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Roman Numerals (Excel 2000)

    The code below, placed in the Worksheet Change event routine in the module behind the worksheet will do what you want in column A. The code would need to be modified to work on a different column or range. Try entering numbers in column A of Sheet1 in the attached workbook.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A:A"))
    If (oCell.Value <> "" And IsNumeric(oCell.Value)) Then
    oCell.Value = Application.WorksheetFunction.Roman(oCell.Value)
    End If
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Roman Numerals (Excel 2000)

    This works well in itself, but it means that the numbers can't be used in calculations any more. The OP stated "The underlying data needs to be in ordinary 'arabic' integer form".

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Roman Numerals (Excel 2000)

    If he needs to do calculations with the values, you are correct. I was reading that to mean he wanted to enter the values as aribic numbers.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •