Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
I am trying to create a custom number format that does the same thing as
=Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
Try:
"###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
I am sad to say that this did not work.
The problem is leading zeros. 1234 should convert to 000-00-1234, and 12300000 should convert to 123-00-0000. "JLGWhiz" wrote: Try: "###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
That is why it is called a custom number format. It only *formats* numbers.
A formatted number will still be a number. there is no custom number format that will convert a number to text. Without knowing what you are doing, it is unclear why text is the requirement. -- Regards, Tom Ogilvy "ToddEZ" wrote: I am sad to say that this did not work. The problem is leading zeros. 1234 should convert to 000-00-1234, and 12300000 should convert to 123-00-0000. "JLGWhiz" wrote: Try: "###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
The two main reasons for using the text "000-00-0000" format are, appearance
and vlookup's (linked to other text "000-00-000" formated data). I am just looking for an easy way to convert the numbers without having to insert a column, run the =text(a1, "000-00-0000") formula and then copy/paste specials as values over the original data. Although this is an easy thing to do, I am trying to make it simple for non-excel experienced users (and safe myself a little time in the process). "Tom Ogilvy" wrote: That is why it is called a custom number format. It only *formats* numbers. A formatted number will still be a number. there is no custom number format that will convert a number to text. Without knowing what you are doing, it is unclear why text is the requirement. -- Regards, Tom Ogilvy "ToddEZ" wrote: I am sad to say that this did not work. The problem is leading zeros. 1234 should convert to 000-00-1234, and 12300000 should convert to 123-00-0000. "JLGWhiz" wrote: Try: "###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
On Fri, 16 Mar 2007 09:08:03 -0700, ToddEZ
wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... Well, that's what a format does! IT doesn't change a value from numeric to text; it changes how the number appears. That is why it is called a "format" or, more specifically, a numeric format -- it formats numbers. If you need to store a value as text, you must enter it as text, or convert it to text using some function. --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
You can use the custom format to make the cell look pretty.
And then you could use =text() in the formulas where you're matching on text. For example: =vlookup(text(a1,"000-00-0000"),sheet2!a:b,2,false) (assumes that the data in column A of sheet2 is really text--not just a number nicely formatted.) ToddEZ wrote: The two main reasons for using the text "000-00-0000" format are, appearance and vlookup's (linked to other text "000-00-000" formated data). I am just looking for an easy way to convert the numbers without having to insert a column, run the =text(a1, "000-00-0000") formula and then copy/paste specials as values over the original data. Although this is an easy thing to do, I am trying to make it simple for non-excel experienced users (and safe myself a little time in the process). "Tom Ogilvy" wrote: That is why it is called a custom number format. It only *formats* numbers. A formatted number will still be a number. there is no custom number format that will convert a number to text. Without knowing what you are doing, it is unclear why text is the requirement. -- Regards, Tom Ogilvy "ToddEZ" wrote: I am sad to say that this did not work. The problem is leading zeros. 1234 should convert to 000-00-1234, and 12300000 should convert to 123-00-0000. "JLGWhiz" wrote: Try: "###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Number Format Excel 2003
If you are looking up a string that looks like 000-00-0000 - assume it is
in B9 =Vlookup(Substitute(B9,"-")*1,Sheet2!A:C,3,False) Column A in sheet2 contains numbers. -- Regards, Tom Ogilvy "ToddEZ" wrote in message ... The two main reasons for using the text "000-00-0000" format are, appearance and vlookup's (linked to other text "000-00-000" formated data). I am just looking for an easy way to convert the numbers without having to insert a column, run the =text(a1, "000-00-0000") formula and then copy/paste specials as values over the original data. Although this is an easy thing to do, I am trying to make it simple for non-excel experienced users (and safe myself a little time in the process). "Tom Ogilvy" wrote: That is why it is called a custom number format. It only *formats* numbers. A formatted number will still be a number. there is no custom number format that will convert a number to text. Without knowing what you are doing, it is unclear why text is the requirement. -- Regards, Tom Ogilvy "ToddEZ" wrote: I am sad to say that this did not work. The problem is leading zeros. 1234 should convert to 000-00-1234, and 12300000 should convert to 123-00-0000. "JLGWhiz" wrote: Try: "###-##-###0" "ToddEZ" wrote: I am trying to create a custom number format that does the same thing as =Text(A1, "000-00-0000"). I suppose an addin might suffice, but I would perfer a custom number format. "000-00-0000" does not work, because it does not store the value as text. Any ideas? thanks... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
Excel 2003, European number format | Excel Discussion (Misc queries) | |||
excel 2003 no brackets in number format | Setting up and Configuration of Excel | |||
Excel Format Cells - Number Tab - Custom - Order of Type Window. | Excel Discussion (Misc queries) | |||
How do I create a custom number format in Excel using a symbol? | Excel Discussion (Misc queries) |