Adding special symbols to the number format in Microsoft Excel 2010

In this article we will learn about how to add special symbols to the number format in Microsoft Excel 2010. We can create so many symbols in excel like check, degree, rupee, tick & many more.

 

To add special symbols to the number format, such as symbols that are not included in the currency or accounting formats in Microsoft Excel 2010 and 2013.

There are two different ways to add the special symbol to the number format.

1st CHAR formula

2nd Custom Format
CHAR: - This formula displays the symbol. To add a symbol to the new format you create, copy the symbol to the Type box.

Syntax of “CHAR” function:          =CHAR(number)
Example:Range A1:A10 contains the number 33 to 42. To return the special character, follow below given steps:-

  • Select the cell B1 and write the formula
  • =CHAR(A1), press enter.
  • The function will return special character.
  • To return the special character for all numbers copy the formula by pressing the keyCtrl+C and paste it into the range A2:A10 by pressing the key Ctrl+V on your keyboard.

img1

Custom Format: - We can use Custom Number Format, to display a Number in Million or Display it in some Color. It’s a tool for old versions of excel when there was no Conditional Formatting option in Excel. All conditional options are performed by using Custom Number Formal.
Add special symbols to the number format by using CHAR formula.

For Example: We have product data with the amount. We want to format the amount in the Euro symbol.

img2

To add the symbol with amount follow below given steps:-

  • Select the cell D4 and write the CHAR formula.
  • =CHAR(128)&" "&C4, press enter.
  • The function will update the amount with Euro symbol.
  • Copy the same formula by pressing the key Ctrl+C and paste into the range D5:D13 by pressing the key Ctrl+V on your keyboard.

img3

Add special symbols to the number format by using the Custom Format

To add the symbol with the amount through Custom Format follow below given steps:-

  • Select the range C3:C13 and copy by pressing the key Ctrl+C and paste in the range E3:E13 by pressing the key Ctrl+V on your keyboard
  • Select the range E3:E13 and press the key Ctrl+1 on your keyboard.
  • A format Cells dialog box will appear.

img4

  • In the Number Tab, Click on Custom.
  • After clicking on the Custom, related options will get appear.
  • In the type box write the format €” “#,##0 to add the euro symbol.

img5

  • The function will update the amount with Euro symbol.

img6

This is the way to update the special symbols with numbers in Microsoft Excel.

Users are saying about us...

  1. The more than 6 digit numbers is like that 1,00,000.00 should come. but in excel it is coming in number format 100,000.00 how to correct it

  2. Hi. I have two questions

    1. If I need to add up numbers together with their symbols (i.e 37.5?c and 14.9?c) and still reflect the symbols ?c in the results...how do I do that?

    2. If a symbol is in front of the number and a symbol behind as well(i.e ±1?) and we need to add or subtract a number that has a unit attached to it(i.e 37.5?c) from the number that has the +- symbol attached (i.e ±1?), and still reflect the symbols ?c in the results, how do we do that?

  3. Hi,

    Please look at date given below which is in no and i want to change into the date format by using the excel formula
    20180928, want into 2018/09/28 format

    could anyone tell me how is this possible to change into the date format.

    thanks
    Vishal Goel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube