Custom Number Format Excel 2003

[ToddEZ]

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...

 

[JLGWhiz]

Try:
"###-##-###0"

 

[ToddEZ]

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.

 

[Tom Ogilvy]

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.

 

[ToddEZ]

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).

 

[Ron Rosenfeld]

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

 

[Dave Peterson]

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.)

 

[Tom Ogilvy]

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.