removing dashes from numbers

[Guest]

Look at EDIT=>Replace.

 

[Pete_UK]

Highlight the column and do Find & Replace (CTRL-H):

Find What: -
Replace with: (leave empty)

Click Replace All.

Note that if you have "numbers" like 0012-3456, you will lose the
leading zeros and end up with 123456.

Hope this helps.

Pete

 

[Guest]

is the dash really there or is the dash added though formatting?
If they act like numbers when you add them, probably
<format><cell><number><general> will get rid of them

 

[Pat Jones]

Hi;

I have a long column of numbers that contain "-" (I call them dashes). I
need to remove the dashes from these 3000+ numbers. Is there a way to
automate this process?

Thanks;

Pat

 

[vezerid]

The following formula will return text and thus preserve the 00's.

=SUBSTITUTE(A2,"-","")

HTH
Kostis Vezerides

 

[Mark Lincoln]

Use an empty column. If your first number is in A1, use this formula
in the first row of your helper column:

=LEFT(A1,FIND("-",A1)-1)&RIGHT(A1,LEN(A1)-FIND("-",A1))

This will force the cell containing this formula to Text format. Copy
the formula down as far as you need, then copy all the new cells, and
without changing your selection, click Edit|Paste Special (Values).
Then you can copy the resulting values over your old column.

Mark Lincoln

 

[Mark Lincoln]

Much simpler and more elegant than my example. I'll have to remember
this.

Mark Lincoln

 

[Pat Jones]

Thanks Pete;

That works except for one thing that I didn't realize was important - many
of these numbers start with a zero (0). The find and replace got rid of the
zeros. I then formatted the cells as text, but the zeros were still gone on
some - but not all - of the results.

Any ideas how to preserve those zeros ?


Cheers;
Pat

 

[Pat Jones]

That's beautiful.

Thanks Kostis

Pat

The following formula will return text and thus preserve the 00's.

=SUBSTITUTE(A2,"-","")

HTH
Kostis Vezerides