Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Number/text Formats

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I need some help to get a Number format in Excelsheets.
I work with artikel numbers in the format 00.0000/0
When I type 201001 -> be comes to 20.1001/0

When I try to get this format in the user formats doesn't this work
How become I this format working??


View Answers     

Similar Excel Tutorials

Format Cells as Text in Excel
This free Excel macro formats a selection of cells as Text in Excel. This macro applies the Text number format to ...
Display Numbers, Dates, & Times in the Correct Format in Excel
In this tutorial I am going to look at Number formatting. Number formatting allows you to change how numbers are di ...
Format Cells in The Number (Numerical) Number Format in Excel
This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that th ...
Format Cells as a Percentage in Excel Number Formatting
This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number f ...

Helpful Excel Macros

Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Format Cells as an Accounting Number in Excel Number Formatting
- This free Excel macro formats a selected cell as an Accounting number. This means that the accounting number format is
Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format

Similar Topics

I just got an address list with phone numbers in different formats: (000)
000-0000 and 000-000-0000. I want to convert them all to one format. The
format cells way does not seem to work and the search in this archive hasn't
yielded any solutions. Can you take an existing list like this and convert
it to a standard format? Thanks ahead for your help.


I have the following list of numbers

0000 0000 0000 0000 0000 0100
0000 0000 0000 0000 0000 0101
0000 0000 0000 0000 0000 0102

and I need to continue the list to

0000 0000 0000 0000 0000 0326

I tried dragging by the grip in the bottom right hand corner but it changes the first didit only.

I tried to put it the numbers in the format


and using a custom format number to put in the extra zeros. This worked fine until I used a countif (or it may of been a lookup) statement in my code to look for 0000 0000 0000 0000 0000 0100 and it can't find it as the number is 100.

Is there away around this that will save me typing in an awful lot of zeros.



I have a text file that I'm working with that includes 2 columns of dates in
the format yyyymmdd, however Excel sees these columns as text and therefore
any formatting I apply as far as date formats doesn't work. I tried custom
format and I get #####. Attempting to use any of the Date formats gets me no
change at all.

My end goal is to convert these two columns of 'dates' to display Julian Day
format, but in order for any of the formulas I've found to work, Excel must
see these as true dates.


I'm trying to select the appropriate format for a cell that contains a number. The format available in excel have a selection of custom formats whose description are a series of #!$-%. But I can't figure out how to make sense of these custom formats. Am I able to create one?

In short, I'm trying to format my numbers so that they have 2 decimal places always, if the value is equal to zero to display a simple dash "-" and if they are negative I would like them to show the negative sign before the number (i.e. -0.23).


Hi Everyone,

Essentially my goal is to create a pivot table with values(numbers), and have them displayed as text.
Number Codes
9 FI-
10 DOA
11 DNA
12 DOP
13 DAO
14 DNO

I learnt today that custom number formats have a limit of two conditions, so [=1]"FIP";[=2]"FOP";[=3]"FNP";[=4]"DIP";[=5]"DIO";[=6]"DOI";[=7]"DNP";[=8]"DIA";[=9]"FI-";[=10]"DOA";[=11]"DNA";[=12]"DOP";[=13]"DAO";[=14]"DNO";General wont work.

However I am unsure why if I have 7 conditional formats

Cond. Format 1
Rule - Format only cells that contain cell value 1,2
Custom Format - [=1]"FIP";[=2]"FOP";General

Cond. Format 2
Rule - Format only cells that contain cell value 3,4
Custom Format - [=3]"FNP";[=4]"DIP"


it still will not work.

Would someone mind shedding some light, or does anyone have another work around for displaying text values in a Pivot Table as opposed to numbers.

Thanks heaps


My formats changed in the excel I was working on, and thereofore I copy pasted the format from the same schedule in the previous month.

Though it changed to the format I requred, when I save and close the schedule and reopen it, the format was reversed back to the old format.

Why doesn't my Format painter work? Is there a way to lock it?

Please help!!!

Ok, I have been trying to figure this out for the last hour and i have not been able to.

I want a cell to have the number format to be like this


but if the user was to input 1234 in the cell I want the cell to show


and not


I am sure there is a simple fix for this but I have literally just given up. If someone can help me that would be much appreciated!!!

Side question: is there a way for me to incorporate formats into data validation for example if the user entered 0123456789 instead of 012-345-6789 the data validation error pops up and explains that they MUST type it inone specific format?


I'm an advanced beginner user with excel, can do basic stufff, but if someone with more experience can help me with this problem I've been having, I would really appreciate it.

When I try to format cells with both the time format hh:mm and the custom format h:mm, I get a date in the front of the time. Actually all the time formats insert a date. I can delete it but it's time consuming for all the cells I work with and if I type in a number the format returns to the date then time! I've tried to highlight it all and delete the date, but that doesn't work. How can I get rid of the date and not the time? Please help!

Thank you.

I assumed that V-Lookup recognizes numbers in whichever format they are. This does not seem to be the case, however. Formatting the columns of a variety of entries (text, number) doesn't work. Although after formatting the column to number and the format APPEARS to be number, it is not. And V-Lookup still won't pick it up. Anyone know what a solution to this might be?

Trying to enter a custom cell format to help speed up some quantity takeoffs I am doing today that need to be done by 9 am tomorrow. Here is what I am trying to do for the last hour.

1.Type L1001 into a cell.

2.What to set up a format that gives me L-1001 automatically.

3.Tried to set a custom format with 0-000, @-0000, ""-0000, @"-0000", and some others.

I know this is simple but the more I try the more I get frustrated. Any help is appreciated and would help me with the monstrous amount of entries I have to do today.

Thanks in advance for taking the time to help me.

I am trying to custom format cells to display "10-10-001CM" after only "1010001cm" is typed. I have been able to format it with just the numbers but everytime I enter the text it no longer formats. The number format will always be the same "##-##-###AA" The letters may be CM, CW, or CC. Does anyone have a suggestion on how this custom format code? Any help would be appreciated! Thank you!

I have a form I created that has many textboxes which once filled out and when a button is pressed, copies those values into the respected cells.

My problem is this:

I have one for date - but it only puts it into MM/DD/YYYY format
I have several that are currency - but I get the "convert to number" error
I have several that are Numbers - but I also get the "convert to number" error
I have some that are souposed to be text - these appear to be okay but the formating is off.

The numbers, text and currency don't format like the rest of the sheet. They are differently alligned and slightly bigger text size.

Even if I use the format painter it doesn't work. If I right click on the cell and click "format cells" it shows the right format - but the cell is still showing differently with with "convert to number error"

How can I get it to go right from the form to the workbook in the proper formats?

According to Excel "General format cells have no specific number format." And yet, they do. When a displayed number is more than 10 digits long Excel formats it specifically to scientific notation. Is there a way to change Excel's default General format setting to a more generalized nature?. If this involves a custom format, what would I type in the Custom text box to have a format with all the features of the General format except that it never changes a number to scientific notation?

Hello, I'm writing a macro and I want to be able to make various changes to a format quickly without having to specify every potential iteration of the format to ensure it captures and changes it. The best example to work with would be adding or removing the "$" sign in an accounting formatted number with any number of decimal places. I could test this by checking for each iteration of the format i.e.
"_($* #,##0_);_($* (#,##0);_($* "" - ""??_);_(@_)"
"_($* #,##0.0_);_($* (#,##0.0);_($* "" - ""??_);_(@_)"
"_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"

But I'm wondering if there is a general way to test for general format types (any "_($* #,##0.xxx_);_($* (#,;_($* "" - ""??_);_(@_)" variant). OR make the numberformat constant and add in say a "$" symbol or another decimal (similar to the actual excel buttons).

I have a set of data I'd like to manipulate- the only problem is that there are two date formats in the set. The formats are in this form:

Thursday 18th September 2013
Sat 15th Jun

The column format is set to general- I've tried to switch it to text or date via "format cells" but that doesn't change it. I've even tried multiplying the cells by 1 in a different column and converting to text but that doesn't work either

Is there a code for converting the date formats? Specifically to dd/mm/yyyy.

I have retrieved some data from external source and it put to my
worksheet in one column numbers in text format and if I want to work
with numbers (add, substract, etc.) I need it to convert to number format.
I tried to select column and mark it as number format however it doesn´t
work. Pls help me what to do, how to suceed. I have no chance to change
the format in source.


This must be me, not Excel, but I can't figure it out.

I want a byte value, converted to Hex, displayed as 2 digits.

Have been using

format(Hex(Asc(x)), "0#")

and thought it was working perfectly but suddenly it returned zero !

These all work

? format(Hex(169), "0#") = "A9"
? format(Hex(185), "0#") = "B9"
? format(Hex(4), "0#") = "04"
? format(Hex(255), "0#") = "FF"

But this doesn't

? format(Hex(154), "0#") = "00" ????

It should be "9A", I reckon !

There's other ways to do this, but I'm intriuged why some numbers are
converted correctly and others not?

I have been trying for some time to persuade Excel to format numbers in 'engineering' format. ie a pseudo scientific format where the exponent is alway a multiple of three and the number of digits to the left of the decimal point in the significand is adjusted accordingly. It was apparent from previous traffic that a lot of people are not familiar with this but anyone who has a Hewlett Packard calculator will be familiar with it.

Anyway after several days of trying to write my own macro, I have discovered one of the Exxcel custom formats which seems to do the trick. Just choose "##0.0E+0" from the list of custom formats and Excel seems to do what is required.


Neil Keron

Please help !

I have a vlookup formula that doesn't work and I can only think it is a difference in format in the cell that the data is coming from (table array I think).

The table array column used to have a formula in it but I have used paste special to get rid of the formula and replaced with just values.

The formula works if I over-type the cell (with the same number !) so I can only think it is a format thing but the format looks the same in the cells that don't work and those that do work.




I am trying to create a number format for formatting resistor value in their most commonly displayed form. For example 1,200 ohms would be displayed as 1.2kΩ and 5,000,000 ohms would be displayed as 5MΩ, and finally 5 ohms would be displayed as 5Ω.

I have been able to create a format that formats any 2 contiguous formats but not all three.

for example: [>999999]###.0,,ΜΩ;[<1000000]###.0,kΩ;
or: [>999]###.0,kΩ;[<1000]###Ω

both work.(other similar criteria work as well) But if I try to put 3 conditions describing the desired formatting I get and error (containing no useful info) as I try to close the formatting dialog.

Is there a limit to the number of conditions that can be used. Or something else I'm overlooking.

Any help would be greatly appreciated.



I have a problem with number formatting. I am trying to make numbers display in the following way;

If a number is equal or greater than +1 then it will display as +x and the number will be in blue

If a number if negative and greater than -1 then it will display as -x and the text is in red

If the number falls between these two values I want it to display the text "Near Zero"

Here are the resulting formats in the right hand column.

Number Format 1 +1 4 +4 0.5 Near Zero -0.5 -Near Zero 0 Near Zero 5 +5 -1 -1

Here's the number format...


When I try to change type color, format etc. Sometimes the change is accepted
anyway, and other times it isn't. It occurs in a large spreadsheet with many
formats, conditional formats and type colors. Is there a limit on format
types etc in excel.

How do I create a custom format based on the first currency format so positive numbers will appear green. I know how to get there but I don't know how to type the correct format.

#[Green] -something like that?

I received a file that has dates listed as yyyymmdd and I can't get excel to recognize this format as a date so I can do date calculations on them.

Here is what I've tried:
Under "format cells -> number -> date" I tried a few different formats and just get "#######" back which is logical since they aren't serial numbers.

I also tried: "format cells -> number -> custom" input a new type of date format of yyyy/mm/dd and when I apply the new format to my column i just get "##########" back.

What am I missing? I have 190K rows so would rather not have to do a calculation to create the date but if that is the only way to do it I will....

Thoughts? Thanks!

I know it's been asked a hundred times before, so one more time shouldn't matter.

I want negative numbers in my check register to be displayed in red, maybe bold red. I'm not using a number format but rather an accounting format, so changing the "number" format in Format - Cells - Number doesn't work.

Using the Currency Format will ensure that your numbers will not align evenly down the column, so that's out. Number Format doesn't have a "$" sign, so I don't want to use that either. Accounting is the best, as numbers are even all the way down the sheet and it has the $ sign. Now if I can just get $ (100.00) to show in red, I'd be in business. Red with a minus sign instead of Parenthesis would be even better.