Turn blank spaces into zeros

[adoy73]

I use Excel 2003.

I have a list of various numbers in a column. These should be 10 digit numbers, however the problem is that for the last 5 digits zeros are replaced by spaces. Is there a way to turn each space into zeros?

for instance what should be

"1262301140"

shows up as

"12623 114 "

However in other situations the number is fine:

7206833364

How can right a formula where it will replace zeros where there are spaces, but leave the normal numbers alone?

Thank you.

 

[shippey121]

the second example 7206833364 has a zero why is this fine you question say to remove all zeros ??

does this zero need removing or not, if not you need to state why not and give more examples of when a zero should not be removed

 

[shippey121]

if all zeros are to be removed look at the function SUBSTITUTE

try

=SUBSTITUTE(B26,0,"")

replace B26 with you cell, then use copy and paste special values to remove the formula

HTH

 

[adoy73]

As I mentioned only the last 5 digits have zeros replaced by spaces. But the substitute function worked. Thanks.