remove dashes

[javalover]

Hello Board,

I have a column seven-digit numbers that I need to remove dashes, however, when I use text to columns, delimited, it removes the zeros.

142-056-78 becomes 142 56 78

The goal is to get this number to look like this: 14205678

Thanks for your help!

 

[VoG]

Try

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

 

[Colin Legg]

Hi,

On the final screen in the Text To Columns Wizard, select each column in turn and choose the Text data format for each of them. This will preserve the zeros but note that your numbers will be stored as strings and they will be split into three columns. I assume that's what you want?

 

[javalover]

VoG: your formula worked (I liked how easy), except on this number: 054-780-01
it dropped the zero before 54. (??)

Colin: this worked for me...thank you!

 

[Colin Legg]

Hi,

Just FYI, to keep the leading zero, VoG's formula just needs a little tweak:

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