Concatenation with leading zeros

[Laura K]

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!

 

[Duke Carey]

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

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

 

[Laura K]

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

 

[Laura K]

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.

 

[Duke Carey]

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]

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

 

[David McRitchie]

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.