• 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

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

xlstime

Active Member
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.

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!!
 
Last edited:

pinoy-exceler

New Member
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
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.
 

NARAYANK991

Excel Ninja
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.
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
 

pinoy-exceler

New Member
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.
 

Hui

Excel Ninja
Staff member
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
 

pinoy-exceler

New Member
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

Last edited:

bosco_yip

Excel Ninja
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

Last edited:

pinoy-exceler

New Member
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
Just WOW. Awesome solution! I learned alot with your solution. Thanks alot, Bosco!
 

bosco_yip

Excel Ninja
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

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

bobhc

Excel Ninja
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)


.
 

pinoy-exceler

New Member
Thanks
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
Clever fix. Thanks again to everyone who helped.
 

r1c1

Administrator
Staff member
Interesting formulas everyone.

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

Hui

Excel Ninja
Staff member
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
 

XOR LX

Active Member
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
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
 

Hui

Excel Ninja
Staff member
I have already considered this issue

But thanx

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
 
Top