• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

convert roman numerals to numbers

P

pinoy-exceler

New Member
Hello Excel experts,

Can you help me string together a formula that would convert (classic)Roman numerals to numbers? eg:

MMMCCCLVII to 3357

roman numerals in cells a2:a20 , formula in cells b2:b20

thanks in advance!!
 
try below formula and drag

=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT("1:3999"))),0),0)

* 3999 because in excel Roman function support till 3999 only.

pinoy-exceler said:
Hello Excel experts,

Can you help me string together a formula that would convert (classic)Roman numerals to numbers? eg:

MMMCCCLVII to 3357

roman numerals in cells a2:a20 , formula in cells b2:b20

thanks in advance!!
Click to expand...
 
Last edited:
NARAYANK991 said:
Hi ,

=MATCH(A2,INDEX(ROMAN(ROW($1:$3999)),),0)

will give the same result.

So will :

=MATCH(A2,ROMAN(ROW($1:$3999)),0)

if it is entered as an array formula , using CTRL SHIFT ENTER.

Narayan
Click to expand...

thank you narayan! now if it's not too much trouble, could we convert the result to words without resorting to VBA =)
eg:

MMMCCCLVII to 3357 to "three thousand three hundred fifty seven"

thank you in advance.
 
pinoy-exceler said:
thank you narayan! now if it's not too much trouble, could we convert the result to words without resorting to VBA =)
eg:

MMMCCCLVII to 3357 to "three thousand three hundred fifty seven"

thank you in advance.
Click to expand...
Hi ,

There are formulae available on the Internet for converting numerals to words , and if you are restricting yourself only to integer values between 1 and 3999 , they may well do the job.

In general however , they are not reliable.

If you want to check one out , see this link :

http://chandoo.org/forum/threads/number-in-words.12703/

Narayan
 
Thank you for the link, Narayan. I ended up creating 6 helper columns and a 28x2 matrix to solve the problem. Not very elegant but it works. Thank you all for you help.
 
Daniel Ferry of Excel Hero fame, wrote this in his ExcelHero Linkedin Blog about 4 years ago

I hope I have copied it correctly

====

"Converting numbers to written text is fairly straightforward from VBA. But I have never seen a good worksheet formula technique that does the same. I threw together a formula to do it, but it is a monster. It is included below to get you started. I am certain that are more efficient ways to do this, so let's see who comes up with the best formula.
Here are the rules:
1. A1 holds the number, which can be any INTEGER between 1 and 999999.
2. Create a formula that responds to the value of A1 and returns the ENGLISH written representation.
3. Example: forty-six million one hundred seventeen thousand five hundred twenty-seven
4. For proper English, numbers between 21 and 99 must be hyphenated.
5. Named constants and array constants can be used by the formula, but no other names or VBA.
6. No helper cells.
7. Better formulas are shorter, clearer, use less function calls.

Here is my first attempt, which proves it can be done.

It uses one named constant, three named array constants, and still comes in at 1371 characters.

The named constant is: n0: ="000000000"
The named array constants are:
nwords1: ={"","one","two","three","four","five","six","seven","eight","nine"}
nwords2: ={"","ten","eleven","twelve","thirteen","fourteen","fifteen", "sixteen","seventeen", "eighteen","nineteen"}
nwords3: ={"","","twenty","thirty","forty","fifty","sixty","seventy", "eighty","ninety"}

And, drum roll, here's the beast:
=REPT(INDEX(nwords1,MID(TEXT(A1,n0),1,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),1,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),1,3)>99)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),2,2)>19)*MID(TEXT(A1,n0),2,1)+1)&REPT("-",(--MID(TEXT(A1,n0),2,2)>20)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),2,2)-8)*(--MID(TEXT(A1,n0),2,2)>9)*(--MID(TEXT(A1,n0),2,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),3,1)+1)*((--MID(TEXT(A1,n0),2,2)<10)+(--MID(TEXT(A1,n0),2,2)>19)))&" million ",(LEN(A1)>6)) & REPT(INDEX(nwords1,MID(TEXT(A1,n0),4,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),4,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),4,3)>99)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),5,2)>19)*MID(TEXT(A1,n0),5,1)+1)&REPT("-",(--MID(TEXT(A1,n0),5,2)>20)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),5,2)-8)*(--MID(TEXT(A1,n0),5,2)>9)*(--MID(TEXT(A1,n0),5,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),6,1)+1)*((--MID(TEXT(A1,n0),5,2)<10)+(--MID(TEXT(A1,n0),5,2)>19)))&" thousand ",(LEN(A1)>3)) & INDEX(nwords1,(LEN(A1)>2)*LEFT(RIGHT(A1,3),1)+1)&REPT(" hundred",LEN(A1)>2)&REPT(" ",(LEN(A1)>2)*(--(RIGHT(A1,1))>0))&INDEX(nwords3,(--RIGHT(A1,2)>19)*LEFT(RIGHT(A1,2),1)+1)&REPT("-",(--RIGHT(A1,2)>20)*(--RIGHT(A1,1)>0))&INDEX(nwords2,((RIGHT(A1,2)-8)*(--RIGHT(A1,2)>9)*(--RIGHT(A1,2)<20)))&INDEX(nwords1,(RIGHT(A1,1)+1)*((--RIGHT(A1,2)<10)+(--RIGHT(A1,2)>19)))"

I believe there is an example at: http://excelhero.com/number_words/number_words.xls

There was a good discussion in the Linkedin group where a few people shortened it a little bit, down to around 500 characters I believe
You can read it all here:
https://www.linkedin.com/groups/3843467/3843467-78700058


Good luck
 
AF1QipM-yZhFBpGCFKOPr3yncPBTalu9TDNmodMSZayK
Wow that's one mega formula right there. That's gonna be hard to edit/evaluate when things go south-but nonetheless amazing. anyway, i uploaded my not so elegant solution consisting of (8) helper cells and matrix to match string values against. I know I'm light years away from your skill level so please don't mock my solution. Thank you everyone for all your help.
 

Attachments

  • roman2int2word.xlsx
    168.3 KB · Views: 16
Last edited:
Try this modified formula, after converted Roman numerals to numbers and restricting values between 1 and 3999…………

1] Goto Formula >> Define Name , and define 2 range names :

Name : n_1

Refer to : ={"";" One";" Two";" Three";" Four";" Five";" Six";" Seven";" Eight";" Nine";" Ten";" Eleven";" Twelve";" Thirteen";" Fourteen";" Fifteen";" Sixteen";" Seventeen";" Eighteen";" Nineteen"}

Name : n_2

Refer to : ={"";0;" Twenty";" Thirty";" Forty";" Fifty";" Sixty";" Seventy";" Eighty";" Ninety"}

Then, in D2 formula copy down :

=TRIM(IF(C2>999,INDEX(n_1,1+LEFT(C2))&" Thousand","")&IF(C2>99,INDEX(n_1,1+LEFT(RIGHT(C2,3)))&" Hundred"&IF(RIGHT(C2,2)="00",""," and"),"")&IF(MOD(C2,100)<20,INDEX(n_1,1+RIGHT(C2,2)),INDEX(n_2,1+LEFT(RIGHT(C2,2)))&IF(RIGHT(C2)="0",""," -")&INDEX(n_1,1+RIGHT(C2))))

Regards
Bosco
 

Attachments

  • Roman2Int2Word.xlsx
    113.8 KB · Views: 13
Last edited:
bosco_yip said:
Try this modified formula, after Roman numerals converted to numbers and restricting values between 1 and 3999…………

1] Goto Formula >> Define Name , and define 2 range names :

Name : n_1

Refer to : ={"";" One";" Two";" Three";" Four";" Five";" Six";" Seven";" Eight";" Nine";" Ten";" Eleven";" Twelve";" Thirteen";" Fourteen";" Fifteen";" Sixteen";" Seventeen";" Eighteen";" Nineteen"}

Name : n_2

Refer to : ={"";0;" Twenty";" Thirty";" Forty";" Fifty";" Sixty";" Seventy";" Eighty";" Ninety"}

Then, in D2 formula copy down :

=TRIM(IF(C2>999,INDEX(n_1,1+LEFT(C2))&" Thousand","")&IF(C2>99,INDEX(n_1,1+LEFT(RIGHT(C2,3)))&" Hundred"&IF(RIGHT(C2,2)="00",""," and"),"")&IF(MOD(C2,100)<20,INDEX(n_1,1+RIGHT(C2,2)),INDEX(n_2,1+LEFT(RIGHT(C2,2)))&IF(RIGHT(C2)="0",""," -")&INDEX(n_1,1+RIGHT(C2))))

Regards
Bosco
Click to expand...

Just WOW. Awesome solution! I learned alot with your solution. Thanks alot, Bosco!
 
bosco_yip said:
Try this modified formula, after converted Roman numerals to numbers and restricting values between 1 and 3999…………

1] Goto Formula >> Define Name , and define 2 range names :

Name : n_1

Refer to : ={"";" One";" Two";" Three";" Four";" Five";" Six";" Seven";" Eight";" Nine";" Ten";" Eleven";" Twelve";" Thirteen";" Fourteen";" Fifteen";" Sixteen";" Seventeen";" Eighteen";" Nineteen"}

Name : n_2

Refer to : ={"";0;" Twenty";" Thirty";" Forty";" Fifty";" Sixty";" Seventy";" Eighty";" Ninety"}

Then, in D2 formula copy down :

=TRIM(IF(C2>999,INDEX(n_1,1+LEFT(C2))&" Thousand","")&IF(C2>99,INDEX(n_1,1+LEFT(RIGHT(C2,3)))&" Hundred"&IF(RIGHT(C2,2)="00",""," and"),"")&IF(MOD(C2,100)<20,INDEX(n_1,1+RIGHT(C2,2)),INDEX(n_2,1+LEFT(RIGHT(C2,2)))&IF(RIGHT(C2)="0",""," -")&INDEX(n_1,1+RIGHT(C2))))

Regards
Bosco
Click to expand...


Found error in the above formula and herein the revised formula :

=TRIM(IF(C2>999,INDEX(n_1,1+LEFT(C2))&" Thousand"&IF((RIGHT(C2,3)="000"),"",IF((RIGHT(C2,2)="00")," and",IF(-MID(C2,2,1),""," and"))),"")&IF(-RIGHT(INT(C2/100)),INDEX(n_1,1+LEFT(RIGHT(C2,3)))&" Hundred"&IF(RIGHT(C2,2)="00",""," and"),"")&IF(MOD(C2,100)<20,INDEX(n_1,1+RIGHT(C2,2)),INDEX(n_2,1+LEFT(RIGHT(C2,2)))&IF(RIGHT(C2)="0",""," - ")&SUBSTITUTE(INDEX(n_1,1+RIGHT(C2))," ","")))

Regards
Bosco
 

Attachments

  • Roman2Int2WordR2.xlsx
    130 KB · Views: 11
Try this

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)


.
 
Thanks
bosco_yip said:
Found error in the above formula and herein the revised formula :

=TRIM(IF(C2>999,INDEX(n_1,1+LEFT(C2))&" Thousand"&IF((RIGHT(C2,3)="000"),"",IF((RIGHT(C2,2)="00")," and",IF(-MID(C2,2,1),""," and"))),"")&IF(-RIGHT(INT(C2/100)),INDEX(n_1,1+LEFT(RIGHT(C2,3)))&" Hundred"&IF(RIGHT(C2,2)="00",""," and"),"")&IF(MOD(C2,100)<20,INDEX(n_1,1+RIGHT(C2,2)),INDEX(n_2,1+LEFT(RIGHT(C2,2)))&IF(RIGHT(C2)="0",""," - ")&SUBSTITUTE(INDEX(n_1,1+RIGHT(C2))," ","")))

Regards
Bosco
Click to expand...

Clever fix. Thanks again to everyone who helped.
 
Interesting formulas everyone.

How about ARABIC()? It works in Excel 2013 or above.
 
Thanx Chandoo

I will be discussing the
=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT("1:3999"))),0),0) function in a
Formula Forensics post later this week
 
Hui said:
Thanx Chandoo

I will be discussing the
=MATCH(A2,INDEX(ROMAN(ROW(INDIRECT("1:3999"))),0),0) function in a
Formula Forensics post later this week
Click to expand...

Hi Hui,

Since you've chosen that non-CSE set-up as opposed to the equivalent CSE one, i.e.:

=MATCH(A2,ROMAN(ROW(INDIRECT("1:3999"))),0)

perhaps it might be best to include a discussion regarding the merits of both versions?

Personally, I'm a little worried about the seemingly increasing number of formulas I see which use this technique of inserting one or more additional INDEX functions into a construction solely for the purpose of rendering it non-CSE.

I'm not saying that not having to remember to commit a formula with a certain keystroke combination is not a good thing. Even experienced users of Excel, not to mention beginners, make that mistake from time to time. As such, all things being equal, given a choice between a formula which requires CSE and one which doesn't, it's both sensible and logical to opt for the latter.

In this case, however, all things are not quite equal. I wonder how many of those people who use this technique of incorporating additional, coercing INDEX functions are aware that the resulting construction, although non-CSE, is actually more resource-heavy than the equivalent CSE version? This is, if you think about it, to be expected: the non-CSE version does have an additional function call, after all.

Naturally, the difference between the two set-ups won't amount to much if the number of such formulas within the workbook is small. However, if we're talking about many constructions then the gap in calculation time for the CSE version and the non-CSE-INDEX one starts to widen.

I wouldn't ever go as far as to say that this practice (of inserting one or more additional INDEX functions to avoid CSE) is incorrect. My only concern is that there already appears to exist a deal of misapprehension concerning array formulas in Excel, that they are somehow something to be feared, avoided at all costs even. (On a not inconsiderable number of occasions I have been asked to solve a problem which prohibits the use of array formulas - when asked why, one posted even replied that they had heard that array formulas "can damage your computer"!)

The point is that, required keystroke combination aside (and come on, remembering it isn't that difficult, really!), there is nothing at all to be 'feared' in array formulas. On the contrary, they are wonderful things which open up many new possibilities to the user of Excel. As such, we should be promoting their use as much as possible, rather than seeking ways to avoid them. And, on that charge, the use of INDEX in this way might just be guilty, in my opinion.

Regards
 
I have already considered this issue

But thanx

XOR LX said:
Hi Hui,

Since you've chosen that non-CSE set-up as opposed to the equivalent CSE one, i.e.:
=MATCH(A2,ROMAN(ROW(INDIRECT("1:3999"))),0)

perhaps it might be best to include a discussion regarding the merits of both versions?
Regards
Click to expand...
 
Hui said:
Daniel Ferry of Excel Hero fame, wrote this in his ExcelHero Linkedin Blog about 4 years ago

I hope I have copied it correctly

====

"Converting numbers to written text is fairly straightforward from VBA. But I have never seen a good worksheet formula technique that does the same. I threw together a formula to do it, but it is a monster. It is included below to get you started. I am certain that are more efficient ways to do this, so let's see who comes up with the best formula.
Here are the rules:
1. A1 holds the number, which can be any INTEGER between 1 and 999999.
2. Create a formula that responds to the value of A1 and returns the ENGLISH written representation.
3. Example: forty-six million one hundred seventeen thousand five hundred twenty-seven
4. For proper English, numbers between 21 and 99 must be hyphenated.
5. Named constants and array constants can be used by the formula, but no other names or VBA.
6. No helper cells.
7. Better formulas are shorter, clearer, use less function calls.

Here is my first attempt, which proves it can be done.

It uses one named constant, three named array constants, and still comes in at 1371 characters.

The named constant is: n0: ="000000000"
The named array constants are:
nwords1: ={"","one","two","three","four","five","six","seven","eight","nine"}
nwords2: ={"","ten","eleven","twelve","thirteen","fourteen","fifteen", "sixteen","seventeen", "eighteen","nineteen"}
nwords3: ={"","","twenty","thirty","forty","fifty","sixty","seventy", "eighty","ninety"}

And, drum roll, here's the beast:
=REPT(INDEX(nwords1,MID(TEXT(A1,n0),1,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),1,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),1,3)>99)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),2,2)>19)*MID(TEXT(A1,n0),2,1)+1)&REPT("-",(--MID(TEXT(A1,n0),2,2)>20)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),2,2)-8)*(--MID(TEXT(A1,n0),2,2)>9)*(--MID(TEXT(A1,n0),2,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),3,1)+1)*((--MID(TEXT(A1,n0),2,2)<10)+(--MID(TEXT(A1,n0),2,2)>19)))&" million ",(LEN(A1)>6)) & REPT(INDEX(nwords1,MID(TEXT(A1,n0),4,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),4,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),4,3)>99)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),5,2)>19)*MID(TEXT(A1,n0),5,1)+1)&REPT("-",(--MID(TEXT(A1,n0),5,2)>20)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),5,2)-8)*(--MID(TEXT(A1,n0),5,2)>9)*(--MID(TEXT(A1,n0),5,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),6,1)+1)*((--MID(TEXT(A1,n0),5,2)<10)+(--MID(TEXT(A1,n0),5,2)>19)))&" thousand ",(LEN(A1)>3)) & INDEX(nwords1,(LEN(A1)>2)*LEFT(RIGHT(A1,3),1)+1)&REPT(" hundred",LEN(A1)>2)&REPT(" ",(LEN(A1)>2)*(--(RIGHT(A1,1))>0))&INDEX(nwords3,(--RIGHT(A1,2)>19)*LEFT(RIGHT(A1,2),1)+1)&REPT("-",(--RIGHT(A1,2)>20)*(--RIGHT(A1,1)>0))&INDEX(nwords2,((RIGHT(A1,2)-8)*(--RIGHT(A1,2)>9)*(--RIGHT(A1,2)<20)))&INDEX(nwords1,(RIGHT(A1,1)+1)*((--RIGHT(A1,2)<10)+(--RIGHT(A1,2)>19)))"

I believe there is an example at: http://excelhero.com/number_words/number_words.xls
There was a good discussion in the Linkedin group where a few people shortened it a little bit, down to around 500 characters I believe
You can read it all here:
https://www.linkedin.com/groups/3843467/3843467-78700058


Good luck
Click to expand...

Can the shorter version of this formula be done? Without using a macro?

And also how can we add currency or currency symbol?

(I apologize my English is bad.)
 
Haven't things changed!
Whilst
= ARABIC(RomanNums)
may be the obvious solution
= LET(
arabicNums, SEQUENCE(3999),
XMATCH(RomanNums, ROMAN(arabicNums) ) )
works fine.
 
You must log in or register to reply here.
Back
Top