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

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

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

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

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

On Fri, 16 Mar 2007 09:08:03 -0700, ToddEZ <(E-Mail Removed)>
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

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

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" <(E-Mail Removed)> wrote in message
news8FCD7FD-4A97-49A1-8525-(E-Mail Removed)...
> 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...