Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you ...
Excel Formatting for Zip Codes and Phone Numbers
Display numbers in Excel as zip/postal codes and phone numbers in order to make them easier to read and retain any ...
Format Any Element of a Chart in Excel
In this tutorial I am going to go through the Format tab in more detail and show you how to Format every element of ...

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.


Among the presets formats there is USD for U.S. Dollars. We do not like to use that -- it is dopey -- but we can't use $ alone, because there are Canadian dollars, Australian dollars and New Zealand dollars in our document as well. We would like to have before numers as a choice of formats -- NZ$, CA$, AU$, US$, etc. so that it reads something like US$1,000,000. Is there a way under "Customize" in the format number tab of Format Cells, that I can get it to do that? I have tried brackets and it doesn't seem to work! THANKS!

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?

Hello everyone.

I have a datafeed that has a date format which looks like this
Feb 09 Brent Crude Oil (USD)

I then do a lookup and pull the first 6 letters. and places a price next to that

Feb 09 95.39

I have another vlookup that has the following formula:

And I created a custom format to look just like the one above.

My vlookup wont work...I assume its because one is a text format and the other is a number format.

Does anyone know how to correct?


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 need that cell G12 has the format where x are numbers. I will insert a 14 digit number and I want it to be in the format above.

I tried to format in custom format typing 00.000.000/0000-00 but it has not worked.

Any ideas???


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 a spreadsheet with data in [H]:MM:SS format to show hours, minutes and seconds. I now want to convert this into a new column as number of days using 8.5 hours in a working day.

Any ideas how I do this because when I do a straightforward division using the format above it doesn't work out.

thank you

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.




If I have a series of numbers in Cell A1: A10 like 156789 etc

I want to be able to do 2 kinds of operations:-

a) How to set the format, so that number gets displayed as 156.789 or
let' say 157 . I have seen folks using custom formats but not sure how
to set it up in this case.

b) How to set the format in such a way that we may also be see "157
Thousand" for above case. Is it possible using custom formats. (I will
like to do the same thing for 1234567 as "1.2 Million" and so on)

Please guide me.



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.