Turn blank spaces into zeros

adoy73

New Member
Joined
Mar 14, 2006
Messages
46
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
if all zeros are to be removed look at the function SUBSTITUTE

try

Code:
=SUBSTITUTE(B26,0,"")

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

HTH
 

adoy73

New Member
Joined
Mar 14, 2006
Messages
46
As I mentioned only the last 5 digits have zeros replaced by spaces. But the substitute function worked. Thanks.
 

Forum statistics

Threads
1,141,627
Messages
5,707,495
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top