Nice... works like a charm. I owe you one!!!

George



"Rick Rothstein" wrote:

Try using the TEXT function instead of concatenation...

=TEXT(A1,"00-000-00000-00-00")

--
Rick (MVP - Excel)


"gwaubrey" wrote in message
...
I have another issue which is exactly related... but a little different
twist.

I have a 14 character long number field that has to be parsed. Similar to
what my colleague has experienced, I had to format the numbers to assure
that
there were a correct number of characters per field.

Here is and example of the number:
03103104290000

When I parse this info I need to retain the exact number of characters in
the string as follows (each number is now in a separate column):
03 103 10429 00 00

By formatting the columns with a custom number format I get the desired
result... ON THE SCREEN.

Now I need to add dashes in between each space so the numbers desired
result
would be:
03-103-10429-00-00

If you use the CONCANTONATE function, or &, to string them together using
the following formula:
=A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5

the result is:
3-103-10429-0-0

So, like my friend here, this is driving me nuts. It gets to be a real
pain
when the 3rd column (5 digets) is preceeded by a "0"... then I only have 4
digits in the field.

Any ideas on how to fix this??




"Mike B" wrote:

I need to fill cells 15 charecters in length with zeroes, the cells have
numbers in them already, ex. 45678 needs to read 000000000045678. The
numers
in the cells are not the same length, some are 4 charecters up to 7
charechters. Can this be done?