Condividi tramite

Changing Numeral Shapes in Excel

  • Articolo
  Dear Scenari,

Changing Numeral Shapes in Excel (even if you don’t know the language)

 

Dear Scenari,

I have an Excel file that contains some numeral shapes which I can’t really read or don’t even know which language the numbers belong to! How can I tell what is the language of the numbers I am seeing and how can I change its format?

Sincerely,

Numerally Confused

 

Dear Numerally Confused,

I understand how frustrating this situation can be!  Fortunately there are two ways that you can see the value of the number in the cell. Oh!  By the way some may not know what a numeral shape is. The numeral shapes are the symbols representing numbers which are different for each language such as the Arabic numeral 5, is the equivalent Roman numeral V!

The first option is that you can select the cell and the value of the number in the cell will appear in the Formula bar. (You might have already known this.)

 

Another way, if you don’t want to keep selecting at each cell to see the value, is that you can use the function VALUE () . Now honey, don’t worry – it’s really easy. All you do is key in an adjacent cell: =Value( cell ) .  (With ‘cell’ being the designation of the cell you would like to convert. See the example below). This function will convert a string that represents a number to an actual number; it can convert the numeral shape as well!

 

Oh dear, with all of these methods, I still haven’t answered your question about what language of that numeral shape you are seeing!  Well, Excel doesn’t have any concept of checking the language like WORD does. (And I’m sure you don’t want to always copy the text and paste it in WORD to check the language of the data!)  So, below are the steps for you to do this right in Excel!  Follow me…

1. Select the cell that contains the number digit

2. Click on the Home ribbon tab, and then click at Number button. (Or you could press Ctrl+1).

       

3. On the “Format Cells” dialog box, click the Number Tab, then Custom in the Category list.
You will notice that the selecting format has some kind of code starting with [$- and before the closing square bracket, the last 4 digits are the Hexadecimalvalue which represents Locale language ID.

 

4. Then you can use this Hexadecimal to look up the language in Locale ID (LCID) Chart which is from https://msdn.microsoft.com website.

To make finding that hexadecimal number easier – when the Locale ID  (LCID) Chart appears – just hit CTL-F (to find) and then type that number in the Find box and it will go right to the language that is associated with!

As shown in the picture above, the 041Eis telling us that the numeral shape is a Thai number!

To learn more about some other custom number formats, you can visit the Excel team blog; Custom Number Formats topic.

Are you still with me so far?

OK!  Now, if you want to play around with these codes a little more, you can even change some of these codes to switch the numeral shapes to another language! Oh – this is going to be fun!!

Now sweetie, these Hexadecimal numbers aren’t as complicated as they first appear. As you can see, these codes [$-D00041E] are in fact, from this formula [$-nnccLCID] where

  • nn is the Hexadecimal value of the number shape.
  • cc is the Calendar code.
  • LCID is the four digit Hexadecimal LCID (locale id) of the region.

Let’s just focus on the nn which we will try to change the numeral shape. The table below shows the list of Hexadecimal values that represent numeral shapes.

    

In order to change the numeral shape of any number in the cell, you just simply input the Hexadecimal value from the table above as the first 2 digits of the custom format code. Go ahead; try changing it to some different values from the list just for fun!   For example try: [$-09000000]0 or [$-9000000]0 for Tamil numeral shape.

   

That’s it. Wow!  That was fun, wasn’t it?  I hope you enjoy changing these numeral shapes in Excel.

Now, be sure and let me know if you have any questions!!

Scenari

My, my, I must give special thanks to Sirirat Reinikka for her assistance with this article! Sirirat works with the Office Global Experience Platform team as a Software Development Engineer in Test at Microsoft. Sirirat is originally from Thailand but she and her team work in Redmond, Washington, USA.  Sirirat and her team specifically focus on making sure the Office applications are ‘world-ready’! Assisting Sirirat with this article was Dafna Chen.

The example companies, organizations, products, domain names, email addresses, logos, people and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, or event is intended or should be inferred