How do I convert 999999 to 999-999 in an excel document?

I have copyied a large number of parcel numbers into excel (maybe 3,000) and
I need to change the format in order to use them in another application. For
instance: 999999 needs to become 999-999 OR 99999 needs to become 099999.
How can this be done without changing each entry manually.

Thank you.

Hi Chris

if the numbers could be either 6 characters long or something less than 6 at
one time (ie you don't do a batch of 6 digit numbers and then a batch of 5
and then a batch of 4) then one way is to use a helper column (ie another
column adjacent to your numbers) with the following formula

=IF(LEN(A1)=6,LEFT(A1,3)&"-"&RIGHT(A1,3),TEXT(A1,"000000"))
you can then copy / edit - paste special - values this over the original
data and delete the helper column.
(note, this will change the numbers into text)

If you do a worksheet of 6 digit numbers then you could select the cells use
format / cells - custom and type
###-###
and click OK
if you then had a worksheet of 5 digit numbers you could select them and use
format / cells - custom and type
000000
and click OK
as these two options are only formatting options the numbers will remain
numbers.

hope this helps
Cheers
JulieD


"Chris" <Chris@discussions.microsoft.com> wrote in message
news:8583C51F-C1AA-487E-B38C-B2F42B28D4C8@microsoft.com...
>I have copyied a large number of parcel numbers into excel (maybe 3,000)
>and
> I need to change the format in order to use them in another application.
> For
> instance: 999999 needs to become 999-999 OR 99999 needs to become 099999.
> How can this be done without changing each entry manually.
>
> Thank you.

On Thu, 6 Jan 2005 07:43:04 -0800, "Chris" <Chris@discussions.microsoft.com>
wrote:

>I have copyied a large number of parcel numbers into excel (maybe 3,000) and
>I need to change the format in order to use them in another application. For
>instance: 999999 needs to become 999-999 OR 99999 needs to become 099999.
>How can this be done without changing each entry manually.
>
>Thank you.

If you want a text string that you can then import into another application, in
an adjacent column put a formula of the type:

=TEXT(A1,"[>99999]000-000;000000")

Change A1 to reflect the first cell of your parcel number list.

If you only want to change the FORMAT in Excel (without changing the underlying
number, then select the cells and do:

Format/Cells/Number Custom Type: [>99999]000-000;000000


--ron