MM

August 10, 2017 - by Bill Jelen

To convert Roman numerals to Arabic, use =ARABIC

Watch Video

To convert Roman numerals to Arabic, use =ARABIC
To convert numbers to Roman, use =ROMAN
ROMAN is only valid from 1 to 3999
ARABIC works with any nonsensical mix of IVXLCDM, up to 254 characters
CONCAT - New in Feb 2016
INDEX, RANDBETWEEN
Repeat 254 M with =REPT("M",254)
See my rocket photography at www.ReportSpace.com

Auto-Generated Transcript

Learn Excel from MrExcel Podcast Episode
two thousand mmm well I hey this one's a
couple heat isn't it yeah 2001-2002 came
out when I was recording there's other
ones I said I gotta take a break for two
thousand to do something special right
you know like maybe have a special price
in the booklet here's what I'm gonna do
I'm gonna give away a free excerpt of
the book everything covering episodes
1974 through 2002 it's about a fifth of
the book over 50 pages PDF and I'm going
to get your help in the process of
giving this way because up in episode
2030 and I need some geographic data to
show off power map or 3d maps so I
created a survey here the link to the
survey is down in the YouTube comments
down there or you can type this tinyurl
I want to get to your country and postal
code and will be mapping that data in
2038 everyone who answers i'll send you
a link on friday for the PDF also
there's a question in there it says
what's your favorite Excel tip that you
haven't seen on the podcast now the best
one of those wins an excel guru mission
patch more on that later alright so the
title of the episode is mm or mm and
that of course is the roman numeral for
2,000 but starting at excel 2013 they
introduced this crazy function called
arabic it takes roman numerals and
converts them back all right so we'd see
that mmm is 2000 the book is called mr.
XL XL that XL is 40 it's my 40th book
and it has 40 great tips and in February
the Super Bowl which was last year with
Super Bowl 50 is now going back to Super
Bowl Li and when your friends are
sitting around watching football they're
like what the heck is Li mean you can
pull up this spreadsheet and show them
that Li is 51 right so we've had Roman
for a long time the Roman function has
been in there I always make fun of the
Roman function who the heck could use
the Roman function and the Roman
functions been there but then they added
this silly function that goes backwards
from Roman numerals back to regular
number is also known as Arabic numbers
now here's the thing with Roman roamin
does not work beyond 3999 that's as far
as it goes if you put 4,000 in there or
any number larger than 4,000 it is
be a value error how but the weird thing
about Arabic is that the Arabic function
will deal with anything things that make
no sense at all so here let's just build
a just some random data here so equal
index of all of those valid Roman
numerals up there press f4 to lock that
down and ran between 1 comma 7 that
gives us integers between one and seven
and I'll just get a big string of those
here like this and i'll use the brand
new function that just came out in
February of 2016 of cat cat will
concatenate all that together without me
having to specify each one that is a
beautiful functions though there is a
crazy random Roman numeral that if you
showed that to Caesar he would probably
cut your head off because it is
completely nonsensical but the Arabic
function has no problem trying to decode
that and changing it into a number of
will calculate calculate calculate look
at all these crazy numbers that are
completely invalid but yet the Arabic
function does its best to try and figure
it out you can even equal our EPT of M
comma up to 250 for MS which would give
you 250 4000 again completely illegal in
Roman numeral times they you just
couldn't go above 3999 that was it that
was the limit but the Arabic function is
somehow making it work so hey back to
this survey I'll give you a free PDF for
the first 50 some pages of the book
everything we've covered so far you
might have heard that we moved down to
Florida and since we moved down to
Florida mrs. Excel and I have a great
new hobby working with a company called
we report space and we go out and set up
remote cameras for all the rocket
launches that happen here in Cape
Canaveral this is a string of photos
that we shot from about 150 yards from
the the rocket is it locked we weren't
150 years from rocket we go out the day
ahead of time and we set those cameras
and one of the interesting things about
this whole culture of rocket launch is
it every launch company for every rocket
they have a mission patch right these
mission patches and people collect these
mission patches and so we have
drawer full of these now you know we've
been down here full time for a year at
this point and so the guy who designs
these is a guy named Tim Gagnon you
might know Mon line as KSC artist I went
to him I said hey Tim once you make me a
prize I can give it up my power Excel
seminars when someone gives me a tip
that I never heard of so the new excel
guru mission patch just like all these
rocket missions that's what you can win
for an excel tip that I've never heard
before even in the YouTube comments if
you comment and I've never heard that
before i'll be getting in contact with
you and sending you one of the excel
guru mission patches the old pins we
used to have an animal pins which were
great if you were alive in person but
every time i mailed an enamel pin out
the thing would break the pin would
break off the back it was just
impossible these are nice and flat it'll
work out great alright episode recap we
introduced the Arabic function brand new
and Excel 2013 to convert Roman numerals
to regular numbers of course you always
been able to go to Roman numerals using
the Roman function Roman only works from
1-230 999 Arabic will work with any
nonsensical mix of the characters I VXL
c d or em up to 250 four of those took a
look at kicking cat which we've already
talked about in the podcast new in
February 2016 of course index and ran
between take a moment fill out the
survey i'll send you a first 50 pages of
the book have a chance to win some cool
prizes way i want to thank you for
stopping by we'll see you next time for
another banette cast from MrExcel

Download File

Download the sample file here: Podcast2000.xlsm

Title Photo: Alexas_Fotos / Pixabay