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 Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.