Delete all leading zeros in a workbook?

New Contributor

Hi!

 

Im working with a lot of data in excel using around 200 sheets. I know how to delete all leading zeros using either options menu är kutools on a single sheet. But how to I do this for the whole document?

Doing this for all sheets will take like 2 hours. Surely there must be a better way?

 

// excel noob

 

3 Replies

@Johnbackman 

Hi, I need to ask you a question. Are these all texts in the cells where you have leading zeros?

Can your texts be converted into values (for example with the help of VALUE() or in the menu via Text in Columns)? If so, the leading zeros have taken care of themselves.

If it is text and should remain so, it becomes much more complicated. Maybe you can give us some examples. For example with a screenshot.

 

@dscheikey 

 

Thank u for a fast reply!

It seems like the values are texts. I am collecting data on number of employees in a specific region. Since some regions have more employees their columns gets longer and this results in "0" in the other sheets with fewer employees.

 

So I either need to delete all leading zeros or find a way to just target cells with information within the column across the workbook. 

 

It seems like I dont have permission to post pictures =/

 

I might have figured it out on my own. I targeted all numbers within the workbook and in the "format cell" tab i put "special" "0" = "0;;" and all leading zeros are now gone.

Dont know if this is the correct way to do it but seems like it worked