# 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

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

Last edited:

#### Hui

##### Excel Ninja
Staff member
Xlstime,

I love that solution

#### xlstime

##### Active Member  Xlstime,

I love that solution

#### NARAYANK991

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

Last edited:
• Marc L, deciog and pinoy-exceler

#### pinoy-exceler

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

Thank you xlstime! this is an awesome solution.

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

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

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 :

Narayan

• Thomas Kuriakose

#### 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.

#### xlstime

##### Active Member
Hey Pinoy,

It would be great if you can share with us your solution 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:

Good luck

• Thomas Kuriakose and deciog

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

• 168.3 KB Views: 8
Last edited:
• Thomas Kuriakose and deciog

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

• 113.8 KB Views: 8
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!

• #### xlstime

##### Active Member
Wonderful... thanks to all #### 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

• 130 KB Views: 6
• Thomas Kuriakose and Marc L

#### bines53

##### Active Member
• Thomas Kuriakose

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

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

• NARAYANK991

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