Concatenation with leading zeros
 

I have a column in excel (2002) of numbers with dashes in them (for example:
04-115-5-0606), and I need to take the dashes out. I'm using the
text-to-columns function, then concatenating it back into one column without
the dashes.
Some of the groupings have leading zeroes. I formatted the destination
cells for the text-to-column part to have the correct number of characters,
so they appear correct when they're split up. When I concatenate, however,
the leading zeroes disappear.
Is there a way to concatenate and keep the leading zeroes? Or is there a
btter way to do this?
Thanks!

Using the original values, with the hyphens still in the text, try

=SUBSTITUTE(A1,"-","")

"Laura K" wrote:

I have a column in excel (2002) of numbers with dashes in them (for example:
04-115-5-0606), and I need to take the dashes out. I'm using the
text-to-columns function, then concatenating it back into one column without
the dashes.
Some of the groupings have leading zeroes. I formatted the destination
cells for the text-to-column part to have the correct number of characters,
so they appear correct when they're split up. When I concatenate, however,
the leading zeroes disappear.
Is there a way to concatenate and keep the leading zeroes? Or is there a
btter way to do this?
Thanks!

Thank you so much! That is *way* better!!

"Duke Carey" wrote:

Using the original values, with the hyphens still in the text, try

=SUBSTITUTE(A1,"-","")

New issue: the number without the dashes needs to replace the number with the
dashes *in the original cell*. When I used "substitue" it created a new
column with the dashless numbers.

Using the original values, with the hyphens still in the text, try

=SUBSTITUTE(A1,"-","")

Once you have the formulas all set up correctly, copy the entire range/column
of formulas, select the original values, and use Edit-Paste Special-Values
to replace the original values. Then delete the column with the formulas in
it.

"Laura K" wrote:

New issue: the number without the dashes needs to replace the number with the
dashes *in the original cell*. When I used "substitue" it created a new
column with the dashless numbers.

Using the original values, with the hyphens still in the text, try

=SUBSTITUTE(A1,"-","")

Thank you. That gives me exactly the result I need.

"Duke Carey" wrote:

Once you have the formulas all set up correctly, copy the entire range/column
of formulas, select the original values, and use Edit-Paste Special-Values
to replace the original values. Then delete the column with the formulas in
it.

Hi Laura,
If you intend to enter more "numbers" as text be sure to format
that column as text first. What you have from SUBSTITUTE will remain
as text unless you reenter it such as hitting F2 then ENTER.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Laura K" wrote in message ...
Thank you. That gives me exactly the result I need.

"Duke Carey" wrote:

Once you have the formulas all set up correctly, copy the entire range/column
of formulas, select the original values, and use Edit-Paste Special-Values
to replace the original values. Then delete the column with the formulas in
it.