Changin cell format and view

[Sergio]

Hi,
I usually get part numbers from other people in the format:
006-1002126 or
008-0073140.
I would like to change it to:
0061002126 and
0080073140
I have tried formatting to custom number 0000000000 but the dashes won't get
removed and the text does not change to number.
Thanks
Sergio

 

[Dave Peterson]

Number format applies to numbers.

You could insert a new column and use a formula like:

=substitute(a2,"-","")
to remove the hyphens from A2

Or you could select that range/column
Edit|replace
what: - (hyphen)
with: (leave blank)
replace all

The results will be numbers (assuming no other text in the cells). Next, you'll
want to apply the number format you want.

 

[Luke M]

Try this formula to convert:

=VALUE(SUSBSTITUTE(A2,"-",""))
and then format cell as desired.

 

[Gord Dibben]

Simply Edit>Replace to get rid of the dashes.

Then format to 0000000000

Note: 006-1002126 has a dash followed by a space.

You will have to deal with that also.


Gord Dibben MS Excel MVP