Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transform formula
Hello again,
I am preparing a classic presentation of numbers. I just need it for a catchy test. Is there a macro or any formula to convert whole numbers into Romen numerals ? thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transform formula
Look in Excel help for the ROMAN function.
The result can be displayed in various forms so see help to determine which form you want. -- Biff Microsoft Excel MVP "driller" wrote in message ... Hello again, I am preparing a classic presentation of numbers. I just need it for a catchy test. Is there a macro or any formula to convert whole numbers into Romen numerals ? thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transform formula
I am preparing a classic presentation of numbers.
I just need it for a catchy test. Is there a macro or any formula to convert whole numbers into Romen numerals ? Here are two functions I've posted in the distant past (written for the compiled version of VB, but which work fine in VBA), one for converting to Roman Numbers and the other to convert from Roman Numbers... Function ToRoman(ByVal Number As Long) As String Dim X As Integer Dim DigitIndex As Integer Dim MaxNumeralCount As Integer Dim Digits() As Byte Dim Numerals() As Byte Numerals = StrConv("IVXLCDM", vbFromUnicode) MaxNumeralCount = Number \ 10 ^ (UBound(Numerals) \ 2) Number = Number Mod 10 ^ (UBound(Numerals) \ 2) ToRoman = String$(MaxNumeralCount, _ Numerals(UBound(Numerals))) Digits = StrConv(CStr(Number), vbFromUnicode) For X = 0 To UBound(Digits) DigitIndex = 2 * (UBound(Digits) - X) If Digits(X) = vbKey9 Then ToRoman = ToRoman & Chr$(Numerals(DigitIndex)) & _ Chr$(Numerals(DigitIndex + 2)) ElseIf Digits(X) = vbKey5 Then ToRoman = ToRoman & Chr$(Numerals(DigitIndex + 1)) & _ String$(Digits(X) - vbKey0 - 5, _ Chr$(Numerals(DigitIndex))) ElseIf Digits(X) = vbKey4 Then ToRoman = ToRoman & Chr$(Numerals(DigitIndex)) & _ Chr$(Numerals(DigitIndex + 1)) Else ToRoman = ToRoman & String$(Digits(X) - vbKey0, _ Chr$(Numerals(DigitIndex))) End If Next End Function Function FromRoman(ByVal RomanNumber As String) As Long Dim DigitCount As Integer Dim NumeralCount As Integer Dim Answer() As Long Dim Digits() As Byte Dim RomanNumerals() As Byte RomanNumerals = StrConv("IVXLCDM", vbFromUnicode) Digits = StrConv(UCase$(RomanNumber), vbFromUnicode) ReDim Answer(UBound(Digits)) For DigitCount = UBound(Digits) To 0 Step -1 For NumeralCount = 0 To UBound(RomanNumerals) If Digits(DigitCount) = RomanNumerals(NumeralCount) Then If NumeralCount Mod 2 Then Answer(DigitCount) = 5 * 10 ^ ((NumeralCount - 1) / 2) Else Answer(DigitCount) = 10 ^ (NumeralCount / 2) End If Exit For End If Next Next For DigitCount = UBound(Answer) To 0 Step -1 If DigitCount < UBound(Answer) Then If Answer(DigitCount) < Answer(DigitCount + 1) Then Answer(DigitCount) = -Answer(DigitCount) End If End If FromRoman = FromRoman + Answer(DigitCount) Next End Function -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transform formula
thanks a lot.
just found out that the 4 digit "2010" is equal to a 3 letter numeral "MMX". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transform csv to qif or ofx format? | Excel Discussion (Misc queries) | |||
How I transform a row into a column? | Excel Discussion (Misc queries) | |||
How I transform a row into a column? | Excel Discussion (Misc queries) | |||
Table Transform | Excel Discussion (Misc queries) | |||
Transform a Cell from Formula to Number | Excel Discussion (Misc queries) |