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.

1^st CHAR formula

2^nd 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.

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.

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.

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.

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.

The function will update the amount with Euro symbol.

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

Vishal Goel
October 12, 2018 at 6:54 pm

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

Reply ↓
- Exceltip
  November 30, 2019 at 11:59 am
  
  "I am assuming that your date will always be in this format (four year numbers/two month numbers/two date numbers).
  Let's say your date is in A1 then use this formula =LEFT(A1,4)&""/""&MID(A1,5,2)&""/""&RIGHT(A1,2)
  
  These articles would help you
  https://www.exceltip.com/excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html
  https://www.exceltip.com/excel-text-editing-and-format/join-first-and-last-name-in-excel.html"
  
  Reply ↓
Nnodim Kajah
June 24, 2015 at 6:48 pm

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?

Reply ↓
sudeesh kumar
January 16, 2015 at 11:11 am

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

Reply ↓