MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MM


August 10, 2017 - by Bill Jelen

MM

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


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.