Changin cell format and view

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
 
D

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.
 
L

Luke M

Try this formula to convert:

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top