Changin cell format and view

  • Thread starter Thread starter Sergio
  • Start date Start date
S

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
 
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.
 
Try this formula to convert:

=VALUE(SUSBSTITUTE(A2,"-",""))
and then format cell as desired.
 
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
 
Back
Top