how do i remove hyphens from between numbers
 

how do i remove hyphens from between numbers witout having to edit manually I
have a column of 3000 numbers similar to below.

0-7643-2200-1 becomes 764322001

0-7643-2299-X becomes 076432299X

if i format the column as text and then use the search and replace dialog
box Ctrl-H search for hyphen and replace with nothing it works on numbers
which end in a letter ie -X but will remove the 0 at the beginning of numbers
not ending in a letter ie -1 in my example

Can anybody help
Thanks Ian

ctrl-f--input "-" --all replace

You could use a formula in the next column, then copy and
paste special value over itself:

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

HTH
Jason
Atlanta, GA

-----Original Message-----
how do i remove hyphens from between numbers witout
having to edit manually I
have a column of 3000 numbers similar to below.

0-7643-2200-1 becomes 764322001

0-7643-2299-X becomes 076432299X

if i format the column as text and then use the search
and replace dialog
box Ctrl-H search for hyphen and replace with nothing
it works on numbers
which end in a letter ie -X but will remove the 0 at the
beginning of numbers
not ending in a letter ie -1 in my example

Can anybody help
Thanks Ian

.

You could format the cell so that it shows the leading 0's.

format|cells|number tab|custom category:
0000000000
(It won't change the text cells.)

Or you could use a helper cell and a formula:

=substitute(a1,"-","")
and drag down.
and then convert to values and delete the original column.



ian78 wrote:

how do i remove hyphens from between numbers witout having to edit manually I
have a column of 3000 numbers similar to below.

0-7643-2200-1 becomes 764322001

0-7643-2299-X becomes 076432299X

if i format the column as text and then use the search and replace dialog
box Ctrl-H search for hyphen and replace with nothing it works on numbers
which end in a letter ie -X but will remove the 0 at the beginning of numbers
not ending in a letter ie -1 in my example

Can anybody help
Thanks Ian

--

Dave Peterson

'0-7643-2200-1
'0-7643-2299-X
ctrl-h

try this to change format first and then strip out the -
Sub fixstring()
For Each c In Selection
c.NumberFormat = "@"
c.Value = Application.Substitute(c, "-", "")
Next
End Sub

--
Don Guillett
SalesAid Software

"ian78" wrote in message
...
how do i remove hyphens from between numbers witout having to edit
manually I
have a column of 3000 numbers similar to below.

0-7643-2200-1 becomes 764322001

0-7643-2299-X becomes 076432299X

if i format the column as text and then use the search and replace dialog
box Ctrl-H search for hyphen and replace with nothing it works on numbers
which end in a letter ie -X but will remove the 0 at the beginning of
numbers
not ending in a letter ie -1 in my example

Can anybody help
Thanks Ian