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 Video Tutorials

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.


This is my first post, and I wasn't sure which forum to use. Hope this is the right one.

I have an issue with number formats in Excel. I have a spreadsheet which was created in Excel 2003, but which a colleague & I are now using in Excel 2007. My colleague is in the States, and I am in the UK (if that's relevant!).

When he & I view this spreadsheet, we both see negativef-rmat cells differently. I see them as (for example) "-1234", and he sees them as "(1234)" 0 both in red text

When my colleague views the formatting for the cells, the format is set to "Number" format, with negative numbers in red text and the format "(1234)"

When I view the formatting for the cells, the format is set to "Custom" format, with negative numbers in red and the format "#,##0;[Red]-#,##0"

Why are we seeing the number formats as different, when we are viewing the same sheet with the same version of Excel (2007)?!

I am actually doing a project on OOXML and have delved into the XML underneath. In both cases the cell is referencing number format 116 (@s="116"). There is actually no number format 116 in the styles.xml file.

Is this something to do with Excel 2007 "guessing" number formats, when they are not supplied in the XML?

Would there be a difference as to how my UK copy of Excel 2007 and my colleague's US copy of Excel 2007 "guess" the formulae?

Thanks in advance for any help you can offer.


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 data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.

Is there a formula/way that can solve this problems?

Thanks in advance!

Hi there,

I am trying to work out an equation that would look at the format of a cell, it has nothing to do with the numbers just the actual format. Basically we are looking at consistencies in number formats.

If we use the information below;

asdfre.poijhg 123.jhgf iutyrh.kjfhg oi46fh.cm38fh ce0305.002999 ce305.857468 1237.jghfkei

For the above where there are 6 digits then a . and then 6 digits if this format is correct the i want it to say correct, if not an Error.

I have come up with the following equation to do this

=+IF(OR(LEN(A1)13,MID(A1,7,1)"."),"Error!","Correct Matter format")

However my cell information needs to extend so it looks at the following format

I have tried incorporating a right function but i am having no such luck and its the small things that can drive one insane.

Any help would be appreciated.


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

If you right click on a cell and bring up "Format Cells" and select the number tab; I want the format of the cell to act just like the "Text" option but conditional formatting needs to still work.

In other words when I type in "1.000" in the cell, I want it to read exactly that. If I try to use the "General" format it defaults the cell to "1". I have tried using the "Number" category but I sometimes have numbers such as "1.0" that I don't want to read 1.000 (or whatever I would specify (0.000, 0.0000)

Let me know if this is enough information to answer on.. Thanks!